Oracle Db Link测试

	A库	B库
Db_name	TEST	OASM
Tnsnames	TOOASM

1>在Tnsnames.ora加入

TOOASM =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.19.130)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = OASM)

    )

  )

2>创建数据库连接

create public database link DB_LINK_NAME connect to TargetDatabaseUserName identified by TargetDatabasePassword using 'TargetDatabaseSIDName'; 
注：TargetDatabaseSIDName为该Oracle数据库所在的主机上的tnsnames.ora文件里边定义的数据库连接串（）。  

[oracle@node2 admin]$ export ORACLE_SID=TEST

[oracle@node2 admin]$ sqlplus / as sysdba

SQL> create public database link linktooasm2 connect to system identified by ank88ank using'tooasm'

Database link created.

3>测试db link

SQL> select * from all_users@linktooasm2;

USERNAME                          USER_ID CREATED

INOASM_USER                            63 01-DEC-10

TEST                                   61 29-NOV-10

BI                                     60 29-NOV-10

PM                                     59 29-NOV-10

SH                                     58 29-NOV-10

IX                                     57 29-NOV-10

OE                                     56 29-NOV-10

HR                                     55 29-NOV-10

SCOTT                                  54 30-JUN-05

MGMT_VIEW                              53 30-JUN-05

TEST2                                  62 01-DEC-10

MDDATA                                 50 30-JUN-05

SYSMAN                                 51 30-JUN-05

MDSYS                                  46 30-JUN-05

SI_INFORMTN_SCHEMA                     45 30-JUN-05

ORDPLUGINS                             44 30-JUN-05

ORDSYS                                 43 30-JUN-05

OLAPSYS                                47 30-JUN-05

ANONYMOUS                              39 30-JUN-05

XDB                                    38 30-JUN-05

CTXSYS                                 36 30-JUN-05

EXFSYS                                 34 30-JUN-05

WMSYS                                  25 30-JUN-05

DBSNMP                                 24 30-JUN-05

TSMSYS                                 21 30-JUN-05

DMSYS                                  35 30-JUN-05

DIP                                      9 30-JUN-05

OUTLN                                  11 30-JUN-05

SYSTEM                                  5 30-JUN-05

SYS                                     0 30-JUN-05

30 rows selected.

4>创建同义词，

SQL> create synonym dblinktest for all_users@linktooasm2;

Synonym created.

SQL>select * from dblinktest;

5>查看数据库的链接

SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK'; 

6>删除数据库连接

SQL> drop public database link LINKTOOASM.REGRESS.RDBMS.DEV.US.ORACLE.COM;

Database link dropped