Database links allow users to treat a group of distributed databases as if they were a single, integrated database. Database links tell Oracle how to get one database to another. It requires that Net8 be running on each of the machines (hosts) involved in the remote database access. Net8 is usually started by the database administrator (DBA) or the system manager. The following figure shows two hosts, each running Net8. There is a database on each of the hosts. A database link establishes a connection from the first database (named LOCAL) to the second database (named REMOTE). The database links software that is located in the local database.
Database links specify the following connection information:
→The communications protocol (such as TCP/IP) to use during the connection.
→The host on which the remote database resides.
→The name of the database on the remote host.
→The name of a valid account in the remote database.
→The password for that account.
When used, a database link actually logs in as a user in the remote database and then logs out when the remote data access is complete. A database link can be preivate, owned by a single user or public in which case all users in the local database can use the link.
Using a Database Link for Remote Queries
If you are a user in the local database, you can access objects in the remote database via a database link. For remote tables, use a database link named REMOTE_CONNECT. In the from clause, reference the table name followed by @REMOTE_CONNECT:
SQL> SELECT * FROM worker@REMOTE_CONNECT;
Note: The maximum number of database links that can be used in a single query is set via the OPEN_LINKS parameter in the database's init.ora initialization file. This parameter usually defaults to four.
There are restrictions to the queries that are executed using database links. You should avoid using database links in queries that use the connect by, start with and prior keywords. Some queries using these keywords will work, but most users of tree-structured queries will fail when using database links.
Using a Database Link for Synonyms and Views
You may create local synonyms and views that reference remote objects. To do this reference the database link name, preceded by an @ sign, wherever you refer to a remote table.
SQL> CREATE SYNONYM worker_syn for worker@remote_connect ;
SQL> CREATE SYNONYM worker_skill_syn for Talbot.worker_skill@remote_connect ;
SQL> CREATE VIEW local_employee_view AS SELECT * FROM worker@remote_connect WHERE Lodging = 'ROSE HILL' ;
Using a Database Link for Remote Updates
SQL> UPDATE worker@remote_connect SET Lodging = 'CRANMER' WHERE Lodging = 'ROSE HILL';
ð This update command will use the remote_connect database link to log in to the remote database. It will then update the worker table in that database, based on the set and where conditions specified.
SQL> UPDATE worker@remote_connect
SET Lodging = (SELECT Lodging FROM lodging@remote_connect WHERE Manager = 'KEN MULLER')
WHERE Lodging = 'ROSE HILL';
ð Both table are in remote database.
SQL> UPDATE worker@remote_connect
SET Lodging = (SELECT Lodging FROM lodging WHERE Manager = 'KEN MULLER')
WHERE Lodging = 'ROSE HILL';
ð Here, the updated table is in remote database and lodging table is in local database.
Syntax for Database Links
You can create a database link with the following command:
CREATE [PUBLIC] DATABASE LINK REMOTE_CONNECT
CONNECT TO [Current_User | Username IDENTIFIED BY password]
USING 'Connect String';
Note: To create a database link, you must have CREATE DATABASE LINK system privilege. The account to which you will be connecting in the remote database must have CREATE SESSION privilege. Both of these system privilege are included as part of the CONNECT role in Oracle.
Public and Private Database Links
A public database link is available to the all users in a database. By contrast, a private database link is only available to the user who created it. It is not possible for one user to grant access on a private database link to another user.
SQL> CREATE PUBLIC DATABASE LINK REMOTE_CONNECT
CONNECT TO username IDENTIFIED BY password
USING 'connect string';
Note: To create a public database link, you must have CREATE PUBLIC DATABASE LINK system privilege. This privilege is included in the DBA role in Oracle.
.
Default and Explicit Logins
In place of the connect to ... identified by ... clause, you can use connect to current_user when create a database link. If you use the crrent_user option, then when that link is used, it will attempt to open a session in the remote database that has the same username and password as the local database account. This is called a default login, since the username/password combination will default to the combination in the local database.
SQL> CREATE PUBLIC DATABASE LINK REMOTE_CONNECT
CONNECT TO current_user
USING 'connect string';
An explicit login specifies a username and password that the database link will use while connecting to the remote database.
SQL> CREATE PUBLIC DATABASE LINK REMOTE_CONNECT
CONNECT TO warehouse IDENTIFIED BY warehouse
USING 'connect string';
ð In the remote database, a user named warehouse was created and was given the password warehouse.
Connect String Syntax
The connection details for these service names are contained in files that are distributed to each host in the network. When a service name is encountered, Oracle checks the local Net8 configuration file (called tnsnames.ora) to determine which protocol, host name and database name to use during the connection.
When using Net8, you must know the name of the service that points to the remote database. For example, if the service name HQ specifies the connection parameters for the database you need, then HQ should be used as the connect string in the create database link command.
SQL> CREATE PUBLIC DATABASE LINK REMOTE_CONNECT
CONNECT TO current_user
USING 'HQ';
A typical entry in the tnsnames.ora file (for a network using the TCP/IP protocol) is shown in the following listing:
HQ = ( DESCRIPTION =
(ADDRESS_LIST =
( ADDRESS = ( PROTOCOL = TCP )
(HOST = host1)
(PORT = 1521) )
)
( CONNECT DATA =
(SERVICE_NAME = HQ.host1)
)
)
ð In this listing, the HQ service name is mapped to a connect descriptor that tells the database which protocol to use (TCP/IP), and which host (host1) and database (HQ) to connect to.
Different protocols will have different keywords, but they all must convey the same content.
Using the User Pseudo-column in Views
The user pseudo-column is very useful when you are using remote data access methods. For example, you may not want all remote users to see all records in a table.
In this example, the name table is queried. If the value of its Name column is the same as the name of the user entering the query, then records will be returned.
SQL> CREATE or REPLACE VIEW restricted_name AS SELECT * FROM name WHERE Name = User;
ð When restricting remote access to the rows of your table, you should first consider which columns would be the best to use for the restriction.
For another example, assume the departments are called NORTH, EAST, SO
UTH and WEST. For each of the departments, a specific database link would be created.
SQL> CREATE DATABASE LINK SOUTH_LINK CONNECT TO SOUTH IDENTIFIED BY PASSWD USING 'HQ';
SQL> CREATE or REPLACE VIEW restricted_worker AS SELECT * FROM worker WHERE Department = User;
ð A user who connects via the SOUTH_LINK database link - and thus is logged in as the south user - would only be able to see the worker records that have a Department value equal to 'south';
Dynamic Links: Using the SQLPLUS Copy Command
To break the transaction into smaller entries, use the SQLPLUS copy command, which has the following syntax:
SQL> COPY FROM
[REMOTE username/remote password@connect string]
[TO username/password@connect string]
TABLE name USING subquery;
ð If the current account is to be the destination of the copied data, then the word to plus the local username, password, and connect string are not necessary. If the current account is to be the source
of the copied data, then the remote connection information for the data source is not necessary.
To set the transaction entry size, use the SQLPLUS set command to set a value for the arraysize parameter. This determines the number of records that will be retrieved in each batch. The copycommit parameter tells SQLPLUS how many batches should be committed at one time.
In this example, the data is committed after every 1,000 records. This reduces the transaction's rollback segment entry size needed form 100MB to 1MB.
SET COPYCOMMIT 1
SET ARRAYSIZE 1000
COPY FROM TALBOT/LEDGER@HQ -
CREATE WORKER -
USING -
SELECT * FROM WORKER
Note: Except for the last line, each line in the copy command must be terminated with a bash (-), since this is a SQLPLUS command.
Data options for the COPY command are given below,
| Option | Description |
| APPEND | Inserts the rows into the description table. Automatically creates the table if it does not exist. |
| CREATE | Create the table and then inserts the rows. |
| INSERT | Inserts the rows into the destination table if it exists; otherwise, returns an error. When using INSERT, all columns must be specified in the using subquery. |
| REPLACE | Drops the existing destination table and replaces it with a new table containing the copied data. |
Connecting to a Remote Database
You can go directly to a remote database. To do this, enter your username and password along with the Net8 connect string for the remote database:
SQLPLUS username/password@HQ
ð This cmmand will log you in directly to the HQ database.
As figure shows, there are very few hardware requirements for the Branch host. All it support is the front-end tool and Net8 - a typical configuration for client-server applications.
