Oracle build DB-LINK

Oracle build DB-LINK

Test conditions

Two servers

Remote: IP: 192.168.0.18, SID: usertest, usertest, password: 123456

Local: IP: 192.168.8.90, SID: wjn, Username: userwjn, password: mosquito0

Parameter settings

1. view the database global_name

Execute select * from global_name;

Remote database 18 return: USERTEST. US. ORACLE. COM

Return value for the local database 90: DB_WJN.REGRESS.RDBMS.DEV.US.ORACLE.COM

2. view the global_name Parameter

SQL> show parameterglobal_name;

NAME TYPE VALUE

-----------------------------------------------------------------------------

Global_names boolean FALSE

If this parameter is set to TRUE, the name of the local connection DBLINK must be the same as that of the remote global_name.

3. Check whether Advanced Replication is supported.

View the v $ option view. If Advanced replication is set to true, It is supported. Otherwise, it is not supported.

Select * from v $ option t wheret. PARAMETER like 'advanced replication % ';

Creation Method

1. The first way to create dblink is to configure the database to be remotely accessed in the tnsnames. ora file of the local database.

Create database link USERTEST_18connect to USERTEST identified by xxxx using 'usertest _ 192.168.0.18 ';

USERTEST_18 indicates the dblink name you created, usertest indicates the Instance name of the remote database, and USERTEST/xxxx indicates the user/password that logs on to the remote database. Then access the dual table in remote database 18 through dblink in the local database. The SQL statement is as follows:

Select * from dual @ USERTEST_18;

2. The second method for creating a dblink is that the remote database to be accessed is not configured in the tnsnames. ora file of the local database.

Create public database link USERTEST_18

Connect to USERTEST

Identified by 123456 using

'(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.18) (PORT = 1521 ))

)

(CONNECT_DATA =

(SID = usertest)

(SERVER = DEDICATED)

)

)';

The second is to put the first information configured in the tnsnames. ora file directly after the dblink statement is created. In the first case, the information in the tnsnames. ora file is as follows:

USERTEST_192.168.0.18 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.18) (PORT = 1521 ))

)

(CONNECT_DATA =

(SID = usertest)

(SERVER = DEDICATED)

)

)