联系:手机/微信(+86 17813235971) QQ(107644445)
标题: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. --不同用户时,需要指定源端表所属用户
Data pump 中network_link参数的使用
https://www.xifenfei.com/2011.html