12c data punp克隆用户的分析

这篇文章给大家介绍12c data punp克隆用户的分析，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。有时候生产环境会出现这样的需求，将用户及其对象和数据移动到其他数据库测试，并且需要为该用户重命名。这时候可以采用数据泵中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=hrExport: Release 12.1.0.1.0 – Production on Mon Feb 9 15:21:47 2015Copyright (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 ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsStarting “DP”.”SYS_EXPORT_SCHEMA_01″: dp/******** directory=dp_dir dumpfile=hr.dmp logfile=hr.log schemas=hrEstimate in prog免费主机域名ress using BLOCKS method…Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 448 KBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/PROCEDURE/PROCEDUREProcessing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/TRIGGERProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing 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 rowsMaster table “DP”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded******************************************************************************Dump file set for DP.SYS_EXPORT_SCHEMA_01 is:/home/oracle/hr.dmpJob “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_newImport: Release 12.1.0.1.0 – Production on Mon Feb 9 15:23:13 2015Copyright (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 ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsMaster table “DP”.”SYS_IMPORT_FULL_01″ successfully loaded/unloadedStarting “DP”.”SYS_IMPORT_FULL_01″: dp/******** directory=dp_dir dumpfile=hr.dmp remap_schema=hr:hr_newProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing 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 rowsProcessing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRAN免费主机域名TProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/PROCEDURE/PROCEDUREProcessing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/TRIGGERProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type SCHEMA_EXPORT/STATISTICS/MARKERJob “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 sysdbaSYS@ora12c >select username from dba_users where username like ‘HR%’;USERNAME——————————————————————————–HRHR_NEWHR_NEW_2使用最新克隆的hr_new_2并且只包含元数据的用户登录，密码和克隆用户相同也是hrSYS@ora12c >conn hr_new_2/hrConnected.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 count(*) from employees;COUNT(*)———-0
关于12c data punp克隆用户的分析就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

相关推荐: 在powerdesigner使用实体的快捷方式有哪些

这篇文章将免费主机域名为大家详细讲解有关在powerdesigner使用实体的快捷方式有哪些，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。 ctrl+V 是又新建了一个与复制的实体相同的一个实体，相当于一个新的实体(实体名称…