dblink test


1.the function of database link
两台不同的数据库服务器,从一台数据库服务器的一个用户读取另一台数据库服务器下的某个用户的数据,这个时候可以使用dblink

2.在A库上创建一个database link,访问B库的链接。然后用expdp把远端,通过dblink相连接的B库中scott用户的数据导出出来。
远端的一个数据库实例情况为:
(1)instance name:orcl
(2)本地配置的tns解析远端的名称为orcl
(3)远端被连接的用户信息scott

create public database link linkto123 connect to scott identified by tiger
using ‘(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.123)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))’
//tnsping orcl是能够得到如下的信息,也就是能够解析的。

(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.123)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))

SQL> create public database link linkto123 connect to scott identified by tiger
2 using ‘(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.201.123)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))’;

Database link created.
//database link 创建完毕了

可以desc scott.emp@dblink_name来看看能够看到远端数据表的数据

//通过dblink访问远端数据库用户下面数据的权限,完全由创建dblink时候,使用的connect用户所限定。

在本地的库上先创建expdp可能需要的directory
用scott用户连接进oracle然后创建目录
SQL> create directory test as ‘/home/oracle/remote_test’
授权
SQL> conn / as sysdba
Connected.
SQL> grant read,write on directory test to scott;

Grant succeeded.

你最好确保expdp过程中两端使用的账户权限是一致的,否则会有下面的报错信息:(当然ora-31631的错误也很可能是因为相关权限的问题)
$ expdp scott/tiger directory=test dumpfile=scott_raw.dmp network_link=linkto123 logfile=expdp.log

Export: Release 10.2.0.4.0 – Production on Tuesday, 18 January, 2011 16:05:32

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user

原来A库中,scott角色是connect,resource,dba。而B库中,scott没人经过任何处理,grant之,赋予权限,再次执行

[oracle@rhel5u4test remote_test]$ expdp scott/tiger directory=test dumpfile=scott_raw.dmp network_link=linkto123 logfile=expdp.log

Export: Release 10.2.0.4.0 – Production on Tuesday, 18 January, 2011 16:09:38

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting “SCOTT”.”SYS_EXPORT_SCHEMA_01″: scott/******** directory=test dumpfile=scott_raw.dmp network_link=linkto123 logfile=expdp.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported “SCOTT”.”DEPT” 5.656 KB 4 rows
. . exported “SCOTT”.”EMP” 7.820 KB 14 rows
. . exported “SCOTT”.”SALGRADE” 5.585 KB 5 rows
. . exported “SCOTT”.”BONUS” 0 KB 0 rows
Master table “SCOTT”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/remote_test/scott_raw.dmp
Job “SCOTT”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 16:10:52