Create a database link remote connection in Oracle

From: http://hi.baidu.com/jiaju111/blog/item/86ccbf4ebb2816ccd0c86a60.html

 

The links between databases are established on the database link. To create a DB link, you must first
Set the link string on each database server.

1. The link string is the service name. First, configure a service name locally. The address points to the remote database address. The service name is the database chain name you will use in the future:

2. Create a database link,
Go to the system administrator SQL> operator and run the following command:

SQL> Create public database link Beijing connect to Scott identified by Tiger
Using 'tobeijing ';

Then a link Beijing with Scott user and Beijing database is created. We query Scott data in Beijing:

SQL> select * from EMP @ Beijing;

In this way, the data of Scott users in Shenzhen and Beijing can be processed as a whole.

3. Create synonyms. In order to make distributed operations more transparent, the Oracle database has a synonym object synonym.

SQL> Create synonym bjscottemp for EMP @ Beijing;

Therefore, you can use bjscottemp to replace the Distributed Link operation with the @ symbol EMP @ Beijing.

4. 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 ';

Yes. Cancel dblink.
3. view database connections
SQL> select owner, db_link from dba_db_links;
Ower db_link
Public test. Us. Oracle. com

4. delete a database connection
First, check the database connection from step 3 and obtain the db_link name.

SQL> drop public database link test. Us. Oracle. com
Database Connection discarded

For example, access the table in database a in database B:

1. Create a database chain in database B:
Create public database link database connection name connect to user name identified by password using 'Connection description'
The connection description must be in the following format:
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = 10.142.10.201) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

2. syntax for accessing objects in the Linked database:
Username. database object @ Database Link name

3. View All database links:
Select owner, object_name from dba_objects where object_type = 'database link ';

4. Example:
Create a database link:
Create public database link dbl_orcl connect to province identified by province_pwd
Using '(description =
(Address_list =
(Address = (Protocol = TCP) (host = 10.142.10.201) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)';

Run the following statement in database B to access the province. tj_ryxx table in database:
Select * from province. tj_ryxx @ dbl_orcl;

 

 

Next, I will add that if dynamic SQL is required to use data links during the stored procedure, an error will be reported. Simple Dynamic SQL is as follows:

 

Strsql varchar2 (800); -- dynamic SQL

.......

Strsql: = '.....';
 
Execute immediate strsql; -- execute dynamic SQL
Commit;