Oracle Builds Db-link

Oracle Builds Db-link

Test conditions

Two servers

Remote: ip:192.168.0.18,sid:usertest, User name: usertest, Password: 123456

Local: IP:192.168.8.90,SID:WJN, user name: Userwjn, Password: mosquito0

Parameter settings

1. View the database Global_name

Execute select * from Global_name;

Remote Database 18 returns: Usertest. US. Oracle.com

Local Database 90 returns: Db_wjn. Regress. Rdbms. DEV. US. Oracle.com

2. View Global_name Parameters

Sql>show Parameterglobal_name;

NAME TYPE VALUE

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

Global_names Boolean FALSE

When this parameter is true, the Dblink name of the local connection must be the same as the remote Global_name.

3. See if advanced replication is supported

View V$option view, advanced replication true support, otherwise not supported

SELECT * from V$option t Wheret. PARAMETER like ' Advanced replication% ';

Building methods

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

Create DATABASE link Usertest_18connect to usertest identified by XXXX using ' usertest_192.168.0.18 ';

Where usertest_18 is the Dblink name you created, Usertest is the instance name of the remote database, and usertest/xxxx is the user/password to log in to the remote database. The dual table in remote database 18 is then accessed through Dblink in the local database, as shown in the SQL statement

SELECT * from [email protected] usertest_18;

2. The second way to create Dblink is to not configure the remote database to be accessed in the local database Tnsnames.ora file

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 that is configured in the Tnsnames.ora file directly behind the Create DBLINK statement. 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)

)

)

Oracle Builds Db-link