Oracle 10g create DB link

In some cases, you need to associate different databases for Data Query and operations.

In Oracle, database links are used to associate tables with different databases ).

There are two ways to create a DB link: Creating an SQL statement and creating it on a visual interface (actually an executed SQL statement ).

1. Create a project using PL/SQL Developer

Name: DBLINK name. For example, DEMO

UserName: Database login Username

Password: Database Login Password

Database:( DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.1) (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = DEMO )))

The above red font content can be queried from the following places: \ product \ 10.2.0 \ client_1 \ network \ admin \Tnsnames. ora

DEMO =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))    )    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = DEMO)    )  )

Remove the above "DEMO =" and remove the linefeeds and spaces from the rest to form a string.

Click "Apply.

After the Database is created, it is displayed under the Database links menu.

Test whether the DB link is valid. Execute the statement: Select * from student @ DEMO.

Student indicates the table name of the remote database.

2. Create an SQL statement

Create public database link V_SERVICE_NAME connect to V_USERNAME identified by V_PASSWORD using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.1) (PORT = 1521 ))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = V_SERVICE_NAME )))';

Where:

V_SERVICE_NAMEIndicates the service name, which can be queried using an SQL statement: SELECT * FROM GLOBAL_NAME; -- view the service name

V_USERNAMEIndicates the account used to log on to the remote database.

V_PASSWORDIndicates the password used to log on to the remote database.

 

PS:According to my test, when the tns string contains a line break, the DB link is invalid. If the DB link name is not a service name, the DB link is invalid.