有时候生产环境会出现这样的需求，将用户及其对象和数据移动到其他数据库测试，并且需要为该用户重命名。这时候可以采用数据泵中remap_schema参数来处理。
数据泵目录已经创建好：dp_dir
数据泵用户已经创建好：dp/dp 权限为dba
测试用户为hr
1. 导出schema为HR的所有内容
[oracle@snow ~]$ expdp dp/dp directory=dp_dir dumpfile=hr.dmp logfile=hr.log schemas=hr
Export: Release 12.1.0.1.0 - Production on Mon Feb 9 15:21:47 2015
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "DP"."SYS_EXPORT_SCHEMA_01": dp/******** directory=dp_dir dumpfile=hr.dmp logfile=hr.log schemas=hr
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "HR"."COUNTRIES" 6.437 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.101 KB 27 rows
. . exported "HR"."EMPLOYEES" 17.06 KB 107 rows
. . exported "HR"."JOBS" 7.085 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.171 KB 10 rows
. . exported "HR"."LOCATIONS" 8.414 KB 23 rows
. . exported "HR"."REGIONS" 5.523 KB 4 rows
Master table "DP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DP.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/hr.dmp
Job "DP"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Feb 9 15:22:17 2015 elapsed 0 00:00:30
导入schema为HR的所有内容，并且重名为hr_new。 如果此时数据库中没有hr_new用户，数据泵会自动创建。
[oracle@snow ~]$ impdp dp/dp directory=dp_dir dumpfile=hr.dmp remap_schema=hr:hr_new
Import: Release 12.1.0.1.0 - Production on Mon Feb 9 15:23:13 2015
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "DP"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DP"."SYS_IMPORT_FULL_01": dp/******** directory=dp_dir dumpfile=hr.dmp remap_schema=hr:hr_new
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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR_NEW"."COUNTRIES" 6.437 KB 25 rows
. . imported "HR_NEW"."DEPARTMENTS" 7.101 KB 27 rows
. . imported "HR_NEW"."EMPLOYEES" 17.06 KB 107 rows
. . imported "HR_NEW"."JOBS" 7.085 KB 19 rows
. . imported "HR_NEW"."JOB_HISTORY" 7.171 KB 10 rows
. . imported "HR_NEW"."LOCATIONS" 8.414 KB 23 rows
. . imported "HR_NEW"."REGIONS" 5.523 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "DP"."SYS_IMPORT_FULL_01" successfully completed at Mon Feb 9 15:23:27 2015 elapsed 0 00:00:12
如果只需要元数据，并不需要数据可以使用content=metadata_only参数来实现
[oracle@snow ~]$ impdp dp/dp directory=dp_dir dumpfile=hr.dmp remap_schema=hr:hr_new_2 content=metadata_only
验证一下克隆结果
[oracle@snow ~]$ sqlplus / as sysdba
SYS@ora12c >select username from dba_users where username like 'HR%';
USERNAME
--------------------------------------------------------------------------------
HR
HR_NEW
HR_NEW_2
使用最新克隆的hr_new_2并且只包含元数据的用户登录，密码和克隆用户相同也是hr
SYS@ora12c >conn hr_new_2/hr
Connected.
HR_NEW_2@ora12c >col tname for a20
HR_NEW_2@ora12c >select * from tab;
TNAME TABTYPE CLUSTERID
-------------------- ------- ----------
EMP_DETAILS_VIEW VIEW
REGIONS TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
COUNTRIES TABLE
8 rows selected.
HR_NEW_2@ora12c >select * from tab;
TNAME TABTYPE CLUSTERID
-------------------- ------- ----------
EMP_DETAILS_VIEW VIEW
REGIONS TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
COUNTRIES TABLE
8 rows selected.
表都被创建出来了，但是表中没有数据，和我们的预期一致！
HR_NEW_2@ora12c >select count(*) from employees;
COUNT(*)
----------
0
全文完
全文完
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29047826/viewspace-1441860/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
12c 数据泵提取建表空间语句和建表语句
下一篇：
12c 数据泵一致性导出
请登录后发表评论
登录
全部评论
