用户需要访问其他数据库或导库时(impdp),需要建立dblink进行连接连接两个数据库!
首先需要赋予用户创建dblink权限,并且dblink只能是public或被用户专有,不能赋予其他用户使用权限。
查看用户权限:
select * from user_sys_privs where privilege like upper('%link%');
创建dblink:
方法一:进入数据库所在系统,修改tnsnames.ora文件,添加net service name
FJNXKF =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.4.143)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = nc61)
)
)
create public database link orcl_3 connect to sz identified by sz using 'FJNXKF';
select * from user_tables@orcl_3;
方法二:是在本地数据库tnsnames.ora文件中没有配置要访问的远程数据库
create public database link orcl_3 connect to sz identified by sz using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = banktax2011)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = bankorcl)
)
)';
方法二不需要访问数据库所在的文件系统,比第一种灵活,但书写复杂。