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 
已经成功导出到客户端指定的位置