1、配置TNS
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.8.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
2、创建链接
SQL> create public database link dblink_name connect to user_name identified by passwd using 'tns_name';
注:
public:对于数据库中的所有用户都是可用的,创建全局数据库必须是sys或system用户;否则仅对创建它的用户可用
dblink_name:创建的dblink名称
user_name:待读取数据库的用户名
passwd:待读取数据库的用户密码
tns_name:该主机tnsname.ora文件配置的待读取数据库连接串
SQL>create public database link testlink connect to uiadm identified by uiadm using 'ORCL';
Database link created
或者使用
SQL>create public database link testlink connect to uiadm identified by uiadm using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.8.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)';
创建成功一个以UIADM为用户和ORCL数据库的链接testlink
SQL> select * from PUBTMSG@testlink;
这样可以把本地和远程数据作为一个整体来处理,查询、删除和插入数据和操作本地的数据库一样,只不过需要表名需要改为"表名@link_name",可通过创建同义词简化
3、创建同义词
SQL> create synonym emplink for emp@testlink;
Synonym created
SQL> select * from emplink;
4、查看所有的数据库链接
SQL> select owner,object_name,status from dba_objects where object_type='DATABASE LINK';
或
SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
------- --------------------------------------- ---------- ------- -----------
PUBLIC TESTLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM UIADM ORCL 2013-08-22
5、删除数据库链接
SQL> drop public database link TESTLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM;
Database link dropped
补充:
创建数据库链接的帐号必须有CREATE DATABASE LINK或CREATE PUBLIC DATABASELINK的系统权限,用来登录到远程数据库的帐号必须有CREATE SESSION权限。这两种权限都包含在CONNECT角色中(CREATE PUBLIC DATABASE LINK权限在DBA中)。
SQL> select * from dba_sys_privs t where t.privilege like upper('%link%');
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
SYS CREATE DATABASE LINK NO
SYS DROP PUBLIC DATABASE LINK NO
DBA CREATE PUBLIC DATABASE LINK NO
若数据库参数GLOBAL_NAME设置为ture,要求数据库链接名称与远端数据库名称一样
SQL> show parameter global_name;
NAME TYPE VALUE
------------------------------------ ----------- -----------
global_names boolean FALSE
修改global参数
SQL>alter system set global_name=false|true;
查看数据库全局名称
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.8.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
2、创建链接
SQL> create public database link dblink_name connect to user_name identified by passwd using 'tns_name';
注:
public:对于数据库中的所有用户都是可用的,创建全局数据库必须是sys或system用户;否则仅对创建它的用户可用
dblink_name:创建的dblink名称
user_name:待读取数据库的用户名
passwd:待读取数据库的用户密码
tns_name:该主机tnsname.ora文件配置的待读取数据库连接串
SQL>create public database link testlink connect to uiadm identified by uiadm using 'ORCL';
Database link created
或者使用
SQL>create public database link testlink connect to uiadm identified by uiadm using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.8.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)';
创建成功一个以UIADM为用户和ORCL数据库的链接testlink
SQL> select * from PUBTMSG@testlink;
这样可以把本地和远程数据作为一个整体来处理,查询、删除和插入数据和操作本地的数据库一样,只不过需要表名需要改为"表名@link_name",可通过创建同义词简化
3、创建同义词
SQL> create synonym emplink for emp@testlink;
Synonym created
SQL> select * from emplink;
4、查看所有的数据库链接
SQL> select owner,object_name,status from dba_objects where object_type='DATABASE LINK';
或
SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
------- --------------------------------------- ---------- ------- -----------
PUBLIC TESTLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM UIADM ORCL 2013-08-22
5、删除数据库链接
SQL> drop public database link TESTLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM;
Database link dropped
补充:
创建数据库链接的帐号必须有CREATE DATABASE LINK或CREATE PUBLIC DATABASELINK的系统权限,用来登录到远程数据库的帐号必须有CREATE SESSION权限。这两种权限都包含在CONNECT角色中(CREATE PUBLIC DATABASE LINK权限在DBA中)。
SQL> select * from dba_sys_privs t where t.privilege like upper('%link%');
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
SYS CREATE DATABASE LINK NO
SYS DROP PUBLIC DATABASE LINK NO
DBA CREATE PUBLIC DATABASE LINK NO
若数据库参数GLOBAL_NAME设置为ture,要求数据库链接名称与远端数据库名称一样
SQL> show parameter global_name;
NAME TYPE VALUE
------------------------------------ ----------- -----------
global_names boolean FALSE
修改global参数
SQL>alter system set global_name=false|true;
查看数据库全局名称
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM