oracle network_link,Data pump 中network_link参数的使用续(登录用户和源端用户不一致处理)...

1、准备工作

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 30 09:08:04 2011

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL> create user test identified by xifenfei;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

2、远程导出数据

[oracle@node1 ~]$ expdp test/xifenfei directory=test_dir dumpfile=t1.dmp network_link=dblink_test tables=chf.T2_1

Export: Release 11.2.0.3.0 - Production on Wed Nov 30 09:09:35 2011

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, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-39087: directory name TEST_DIR is invalid

--对目录需要相关权限

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 30 09:09:59 2011

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL> grant read,write on directory test_dir to test;

Grant succeeded.

SQL> expdp test/xifenfei directory=test_dir dumpfile=t1.dmp network_link=dblink_test tables=chf.T2_1

SP2-0734: unknown command beginning "expdp test..." - rest of line ignored.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

[oracle@node1 ~]$ expdp test/xifenfei directory=test_dir dumpfile=t1.dmp network_link=dblink_test tables=chf.T2_1

Export: Release 11.2.0.3.0 - Production on Wed Nov 30 09:10:23 2011

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, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

ORA-31631: privileges are required

ORA-39109: Unprivileged users may not operate upon other users' schemas

--执行导出用户需要相关权限exp_full_database

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 30 09:11:54 2011

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL> grant exp_full_database to test;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

[oracle@node1 ~]$ expdp test/xifenfei directory=test_dir dumpfile=t1.dmp network_link=dblink_test tables=chf.T2_1

Export: Release 11.2.0.3.0 - Production on Wed Nov 30 09:14:29 2011

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, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=test_dir dumpfile=t1.dmp network_link=dblink_test tables=chf.T2_1

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 22 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "CHF"."T2_1" 9.326 MB 100000 rows

Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for TEST.SYS_EXPORT_TABLE_01 is:

/tmp/t1.dmp

Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 09:14:35

--实现用test登录，导出源端chf用户下面的表

[oracle@node1 ~]$ rm /tmp/t1.dmp

[oracle@node1 ~]$ expdp test/xifenfei directory=test_dir dumpfile=t1.dmp network_link=dblink_test tables=T2_1

Export: Release 11.2.0.3.0 - Production on Wed Nov 30 09:14:57 2011

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, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

ORA-39001: invalid argument value

ORA-39195: At least one schema in the TABLE_FILTER does not exist.

--如果当前登录用户和表所属用户不同，需要指定schema的名称

3、直接导入数据到目标端

[oracle@node1 ~]$ impdp test/xifenfei directory=test_dir network_link=dblink_test REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users tables=chf.t2_1

Import: Release 11.2.0.3.0 - Production on Wed Nov 30 09:45:38 2011

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, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

ORA-31631: privileges are required

ORA-39109: Unprivileged users may not operate upon other users' schemas

--登录用户需要imp_full_database权限

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 30 09:46:41 2011

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL> grant imp_full_database to test;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

[oracle@node1 ~]$ impdp test/xifenfei directory=test_dir network_link=dblink_test REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users tables=chf.t2_1

Import: Release 11.2.0.3.0 - Production on Wed Nov 30 09:46:59 2011

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, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

Starting "TEST"."SYS_IMPORT_TABLE_01": test/******** directory=test_dir network_link=dblink_test REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users tables=chf.t2_1

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 22 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . imported "XFF"."T2_1" 100000 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 09:47:07

--使用test用户登录，目标端用户为xff，源端用户为chf

[oracle@node1 ~]$ impdp test/xifenfei directory=test_dir network_link=dblink_test REMAP_SCHEMA=chf:xff REMAP_TABLESPACE=odu:users tables=t2_1

Import: Release 11.2.0.3.0 - Production on Wed Nov 30 09:52:54 2011

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, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

ORA-39001: invalid argument value

ORA-39195: At least one schema in the TABLE_FILTER does not exist.

--不同用户时，需要指定源端表所属用户