How to Create a database link in Oracle

Multiple Oracle databases physically stored in the network can be logically considered as a single large database. Users can access the data in the remote database through the network, the collaborative processing between servers is completely transparent to workstation users and applications, developers do not need to care about the network connection details, the specific distribution of data on network nodes, and the coordination process between servers.

The links between databases are established on the database link. To create a database link, you must first set the LINK string on the DATABASE that creates the LINK, that is, configure the local network service name of a remote DATABASE.

For the configuration of the link string, see the client connection server section.

You can run the following command to check the Global Database Name:

SELECT * FROM GLOBAL_NAME;

You can use the following statement to modify the parameter value:

Alter system set GLOBAL_NAME = TRUE/FALSE;

<1> when the database parameter global_name is set to false, the Database Link name is not required to be the same as that of the remote database.

Connect and communicate between oracle databases.
The syntax for creating a database link is as follows:
CREATE [PUBLIC] database link link

Connect to username identified by password

USING 'string string'

Where:
-- Demona is the connection string created with net8 easy config.
Set global_names In the init. ora file of the target database to false.
Restart database settings to take effect
Or run the command as sys.

Note: The account used to CREATE the database link must have the system permission of create database link or create public database link. The account used to log on to the remote DATABASE must have the create session permission. Both permissions are included in the CONNECT role (create public database link permission is included in DBA ).

A public database link is available to all users in the database, while a private link is only available to users who create it. It is impossible for a user to authorize a private database link to another user. A database link is either public or private.

When creating a database link, you can also use the default logon mode, that is, do not specify the remote database user name and password:

Create public database link zrhs_link

Using 'zrhs ';

If the user name and password are not specified, ORACLE uses the current user name and password to log on to the remote database.

USING is followed by a link string, that is, the network service name of the remote database. This service name is stored in TNSNAMES. the ORA file defines the protocol, host name, port, and database name.

(If there are two database servers, db_A and db_ B, and user_a under db_A needs to use database link to access the data of user_ B under db_ B, follow these steps: 1. First, user_ B, this user must have select permission. 2. tnsnames on db_ B. ora needs to establish a connection string tnsname_BtoA, which can be connected to db_A from db_ B. 3. Create a connection string tnsname_AtoB in tnsnames. ora on db_A. You can connect to db_ B from db_A. 4. create a db link on db_A in the following format: create database link link_AtoB_name connect to user_ B identified by user_ B password using 'tnsname _ atob '; link_AtoB_name must be the global name (SID) of the remote database, and tnsname_AtoB is the connection string to the remote database. 5. Test whether data can be read. Select * from dual @ link_AtoB_name: dummy x indicates that the setting is successful .)

The statement for deleting a database link is:

DROP [PUBLIC] database link zrhs_link

Database link reference

Generally, when you reference a database link, you can directly put it behind the called table name or view name, and use a @ in the middle as the delimiter:

SELECT * FROM worker @ zrhs_link;

For frequently used database links, you can create a local synonym for ease of use:

Create synonym worker_syn FOR worker @ zrhs_link;

You can also create a local remote view for ease of use:

Create view worker as select * FROM worker @ zrhs_link where... ;

Now this view can be treated like any other view in the local database, and can be authorized to other users to access this view, but this user must have the permission to access the database link.

In another case, the table to be accessed is not under the remote account specified in the database link, but the account has the permission to access the table, add the table username before the table name:

SELECT * FROM camel. worker @ zrhs_link;

<2> when the database parameter global_name = true, the database connection name must be the same as the remote database name.
You can use the following command to check the global name of the database:

SELECT * FROM GLOBAL_NAME;

Atabase link (dblink) -- Database link
Syntax for creating dblink:
SQL> create database link name
Connect to username identified by password
Using 'host string name ';
For example:
SQL> create database link ora9i.us.oracle.com ### here, us.oracle.com is the default domain name of oracle ###
Connect to scott identified by tiger
Using 'sun ';

1) The dblink name must be the same as the global database name (global_name) of the remote database;
2) User name. The password is the remote database user name and password;
3) The host string is the string defined in tnsnames. ora;
4) dblink cannot be created between two databases with the same name;
Then, you can access the remote database through dblink.
For example:
SQL> select * from table name @ ora9i.us.oracle.com;
You can also create snapshots to automatically transmit remote data through dblink.

View All database links, go to the system administrator SQL> operator, and run the following command:

SQL> select owner, object_name from dba_objects where object_type = 'database link ';