位置：首页-资讯-数据库

12c data punp克隆用户的分析

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

这篇文章给大家介绍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=hr

Export: Release 12.1.0.1.0 - Production on Mon Feb 9 15:21:47 2015

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

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_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 count(*) from employees;

COUNT(*)

----------

关于12c data punp克隆用户的分析就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

阅读原文内容投诉