12c 資料泵克隆使用者

shilei1發表於2018-01-23
有時候生產環境會出現這樣的需求，將使用者及其物件和資料移動到其他資料庫測試，並且需要為該使用者重新命名。這時候可以採用資料泵中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 count(*) from employees;

COUNT(*)
----------
0

全文完

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/196700/viewspace-2150454/，如需轉載，請註明出處，否則將追究法律責任。

相關文章