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.
创建dblink，

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>
使用dblink
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>
为dblink创建同义词；隐藏远程数据库对象名字和拥有者信息；

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>
查询dblink
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
user_db_links
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>
all_db_links

 

4、删除dblink

SQL> drop public database linke ora9i_dblink;
View Code

 

 

posted @ 2013-07-27 11:33  ArcerZhang  阅读(242)  评论(0)  编辑  收藏  举报