dblink

参考文章:http://www.cnblogs.com/xd502djj/archive/2009/07/02/1515509.html

1、创建dblink

SQL> show user
USER is "SYS"
SQL> create public database link ora9i_dblink connect to scott identified by tiger using 'ORA9IDB';

Database link created.

create public database link link_name connect to user_name identified by user_password using 'SID';

注意:

　　1、SID必须加引号

　　2、SID要在源数据库的tnsnames.ora中创建.

2、使用dblink

SQL> conn hr/hr
Connected.
SQL> select * from scott.t@ora9i_dblink;

        ID NAME
---------- --------------------
         0 arcerzhang
         1 maryhu
         2 nancyzhang
         3 peterzhang
         4 zhanghuanyi
         5 luofayu
         6 zhangshaoqi
         7 xianjunhua

8 rows selected.

SQL> 

SQL> create synonym ora9i_scott_t for scott.t@ora9i_dblink;

Synonym created.

SQL> select * from ora9i_scott_t;

        ID NAME
---------- --------------------
         0 arcerzhang
         1 maryhu
         2 nancyzhang
         3 peterzhang
         4 zhanghuanyi
         5 luofayu
         6 zhangshaoqi
         7 xianjunhua

8 rows selected.

SQL> 

3、查询dblink信息

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

OBJECT_NAME                                                                                                                      OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -------------------
ORA9I_DBLINK                                                                                                                     DATABASE LINK

SQL> 

SQL> desc user_db_links;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 DB_LINK                                               NOT NULL VARCHAR2(128)
 USERNAME                                                       VARCHAR2(30)
 PASSWORD                                                       VARCHAR2(30)
 HOST                                                           VARCHAR2(2000)
 CREATED                                               NOT NULL DATE

SQL> desc all_db_links;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                 NOT NULL VARCHAR2(30)
 DB_LINK                                               NOT NULL VARCHAR2(128)
 USERNAME                                                       VARCHAR2(30)
 HOST                                                           VARCHAR2(2000)
 CREATED                                               NOT NULL DATE

SQL> 

4、删除dblink

SQL> drop public database linke ora9i_dblink;