Oracle Db Link test

Source database:
Computer Name: bkjia
Database Name: orcl

Target database:
Computer Name: suzhou
Database Name: ocp

0. Create a test table for the target database
SQL> conn scott/password
Connected.
SQL> show user;
The USER is SCOTT"
SQL> create table t1
2 (
3 sid int not null,
4 sno int
5 );

The table has been created.

SQL> insert into t1 values (101,201 );

One row has been created.

SQL> commit;

Submitted.

SQL> select * from t1;

SID SNO
--------------------
101 201

1. Modify tnames. ora on the source database

To_suzhou =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.90.20) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ocp)
)
)

2. Create a link on the source database

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 ().

Create public database link to_sz connect to scott identified by password using 'to _ suzhou ';

3. Test db link

SQL> select * from t1 @ to_sz;

SID SNO
--------------------
101 201

4. Create synonyms,

SQL> create synonym dblinktest for t1 @ to_sz;

Synonym created.

SQL> select * from dblinktest;

5. view the database link

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

6. delete a database connection

SQL> drop public database link to_sz;

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12