客户有一个需求，是将一个库中的某个用户迁移到一台新的oracle服务器上，因数据量较小，并且不涉及版本的升级，所以可以采用创建一个dblink，然后通过这个dblink直接从源库将用户数据导出并导入到新库中。
为了防止现场发生意外，因此先自己搭建一套环境进行测试，环境如下：
源库：192.168.56.100 Abbott 数据库：orcl 连接名：orcl1 导出用户：test
新库：192.168.56.40 ora-ogg 数据库：orcl 连接名：ogg 创建用户：abbott
实施步骤：
- 配置源库和新库的tnsnames.ora文件，使其可以互连，这里源库和新库的tnsnames.ora文件内容相同；
[oracle@Abbott admin]$ vi tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCL1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) OGG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.40)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )
2. 测试连接，从源库使用abbott用户连接到新库上，或者从新库用test用户连接到源库上：
[oracle@Abbott admin]$ sqlplus abbott/abbott@ogg SQL*Plus: Release 11.2.0.4.0 Production on Wed May 24 16:01:36 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
3. 新库创建dblink，这里用sys用户登录，创建了一个public的dblink：
SQL> create public database link sjdr connect to test identified by test using 'orcl1'; Database link created.
4. 在源库授予导出用户test执行数据泵的权限：
[oracle@Abbott admin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed May 24 16:30:58 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> grant EXP_FULL_DATABASE to test; Grant succeeded.
注：如果未授予该权限，在导入导出的时候将报如下错误：
[oracle@ora-ogg admin]$ impdp system/oracle schemas=TEST network_link=sjdr Import: Release 11.2.0.4.0 - Production on Wed May 24 16:24:25 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.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
5. 在新库中创建test用户所对应的表空间及数据文件：
SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test.dbf' size 20m autoextend on; Tablespace created
6. 执行导入导出：
[oracle@ora-ogg admin]$ impdp system/oracle schemas=TEST network_link=sjdr Import: Release 11.2.0.4.0 - Production on Wed May 24 16:33:16 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** schemas=TEST network_link=sjdr Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 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 . . imported "TEST"."T1" 1 rows Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Wed May 24 16:33:30 2017 elapsed 0 00:00:14
7. 检查结果：
[oracle@ora-ogg admin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed May 24 16:33:40 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from test.t1; ID NAME ---------- -------------------- 1 zx
8. 只导用户表结构，不包含表数据，并且将test用户表结构及对象导入到abbott用户下，修改test用户表空间到abbott表空间：
[oracle@ora-ogg admin]$ impdp system/oracle remap_schema=test:abbott remap_tablespace=test:abbott network_link=sjdr content=metadata_only
9. 新库中abbott用户默认表空间为abbott，如果未指定remap_tablespace，而只是指定remap_schema为abbott，那么导入后对象仍然在TEST表空间中：
[oracle@Abbott ~]$ impdp system/oracle remap_schema=test:abbott network_link=impabbott Import: Release 11.2.0.4.0 - Production on Thu May 25 23:26:00 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** remap_schema=test:abbott network_link=impabbott Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"ABBOTT" already exists 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 . . imported "ABBOTT"."T1" 1 rows Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION ORA-39082: Object type ALTER_FUNCTION:"ABBOTT"."VERIFY_FUNCTION_11G" created with compilation warnings Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE ORA-39082: Object type ALTER_PROCEDURE:"ABBOTT"."MM" created with compilation warnings Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 3 error(s) at Thu May 25 23:26:12 2017 elapsed 0 00:00:11 [oracle@Abbott ~]$ sqlplus / as sysdba SQL> conn abbott/abbott SQL> select * from t1; ID NAME ---------- -------------------- 1 zx SQL> select segment_name,tablespace_name from dba_segments where owner='ABBOTT' and segment_name='T1'; SEGMENT_NAME TABLESPACE_NAME ----------------------------------------- T1 TEST SQL> select segment_name,tablespace_name from dba_segments where segment_name='T1'; #一个test.T1 一个Abbott.T1 SEGMENT_NAME TABLESPACE_NAME ----------------------------------------------- T1 TEST T1 TEST
10. 使用sys无密码导入导出：
[oracle@Abbott ~]$ impdp '/ as sysdba' directory=impdir dumpfile=full.dmp logfile=testim2.log remap_schema=test:abbott
11.用户密码包含特殊符号的导入导出
-
[oracle@Abbott ~]$ impdp 'sys/"oracle@#123" as sysdba' directory=impdir dumpfile=full.dmp logfile=testim2.log