Oracle DB Link

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. Configure TNS, $ ORACLE_HOME/Network/admin/tnsname. ora
10 gstandby =
(Description =
(Address = (Protocol = TCP) (host = HFCC-KF-3068) (Port = 1522 ))
(CONNECT_DATA =
(Server = dedicated)
(SERVICE_NAME = 10 gstandby)
)
)

2. Create a database link,
Create public database link db_link_name connect to targetdatabaseusername identified by targetdatabasepassword using 'targetdatabasesidname ';
Note: targetdatabasesidname is the database connection string defined in the tnsnames. ora file on the host where the Oracle database is located.

SQL> Create public database link DVD connect to system identified by system using 'orcl10g'; or use: Create public database link link_name connect to user identified by PWD using '(description =
(Address_list =
(Address = (Protocol = TCP) (host = 192.168.1.1) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)';

A dvd linked to the System user and orcl10g database is created. We query the DVD data:

SQL> select * From all_users @ DVD;

In this way, local and remote data 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 syntest for all_users @ DVD;

So we can use syntest to replace the Distributed Link with the @ symbol to operate all_users @ DVD;

4. View All database links:

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

5. view database connections
SQL> select owner, db_link from dba_db_links;
Ower db_link
Public DVD. Regress. RDBMS. Dev. Us. Oracle. com

6. delete a database connection
First, view the database connection in step 1 and obtain the db_link name.
SQL> drop public database link DVD. Regress. RDBMS. Dev. Us. Oracle. com
Database Connection discarded