expdp/impdp 参数network_link使用测试

expdp/impdp 参数network_link使用测试

下面是network_link常用的三个环境测试

1，，服务器端的数据导出到指定的客户端

2，不同数据库间迁移数据。

3，同一个数据库中不同用户之间迁移数据。

一：服务器端的数据导出到指定的客户端

1，修改客户端的TNSNAMES文件
POWER1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.13)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = power1)
)
)
2，创建dblink
SQL> CREATE PUBLIC DATABASE LINK "POWER1"
2 CONNECT TO scott
3 IDENTIFIED BY "Oracle"
4 USING 'POWER1';

Database link created.

SQL> select * from dual@power1;

D
-
X
3，directory目录
SQL> set lines 170
SQL> col owner for a15
SQL> col directory_name for a60
SQL> col directory_name for a30
SQL> col DIRECTORY_PATH for a70
SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
--------------- ------------------------------ ----------------------------------------------------------------------
SYS DUMP /tmp
SYS TOAD_BDUMP_DIR /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace
SYS XMLDIR /u01/app/oracle/product/11.2/db_1/rdbms/xml
SYS DATA_PUMP_DIR /u01/app/oracle/admin/orcl11g/dpdump/
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2/db_1/ccr/state
如果不存在使用create directory创建再用grant授予用户权限
4，导数据
[oracle11g@rhel4 admin]$ expdp system/oracle directory=dump dumpfile=scott_test.dmp logfile=scott_log.dmp network_link='power1' schemas=scott

Export: Release 11.2.0.3.0 - Production on Tue Mar 26 18:22:28 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit 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


grant这个命令要在源端数据库上面执行
SQL> GRANT exp_full_database TO scott;

Grant succeeded.
回到客户端上面
[oracle11g@rhel4 admin]$ expdp system/oracle directory=dump dumpfile=scott_test.dmp logfile=scott_log.dmp network_link='power1' schemas=scott

Export: Release 11.2.0.3.0 - Production on Tue Mar 26 19:40:03 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/tmp/scott_test.dmp"
ORA-27038: created file already exists
Additional information: 1


[oracle11g@rhel4 admin]$ expdp system/oracle directory=dump dumpfile=scott_test.dmp logfile=scott_log.dmp network_link='power1' schemas=scott REUSE_DUMPFILES=Y

Export: Release 11.2.0.3.0 - Production on Tue Mar 26 19:42:29 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dump dumpfile=scott_test.dmp logfile=scott_log.dmp network_link=power1 schemas=scott REUSE_DUMPFILES=Y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.129 GB
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/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."TEST" 1.800 GB 19096576 rows
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/tmp/scott_test.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:46:1
已经成功导出到客户端指定的位置