[20170203]克隆schema.txt
--开发要求在原有数据库的基础上克隆schema(在相同的数据库上),仔细想一下很简单,采用impdp+dblink的模式可以完成.
--在测试环境测试看看.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//建立一个loopback连接
CREATE PUBLIC DATABASE LINK LOOPBACK USING 'localhost:1521/book';
--//测试dblink
SCOTT@book> select sysdate from dual@loopback;
SYSDATE
-------------------
2017-02-03 16:19:48
2.克隆:
--//首先建立用户:
CREATE USER ztest IDENTIFIED BY ztest;
GRANT DBA TO ztest;
$ impdp system/oracle directory=DATA_PUMP_DIR network_link=loopback REMAP_SCHEMA=scott:ztest REMAP_TABLESPACE=users:users SCHEMAS=scott
--//注意后面一定要加SCHEMAS=scott!!
Import: Release 11.2.0.4.0 - Production on Fri Feb 3 16:42:21 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/a* directory=DATA_PUMP_DIR network_link=loopback REMAP_SCHEMA=scott:ztest REMAP_TABLESPACE=users:users SCHEMAS=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.375 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ZTEST" 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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "ZTEST"."SESSION_WAIT_RECORD" 8122 rows
. . imported "ZTEST"."LOCK_OBJECT_RECORD" 8122 rows
. . imported "ZTEST"."DEPT" 4 rows
. . imported "ZTEST"."DEPTX" 4 rows
. . imported "ZTEST"."EMP" 14 rows
. . imported "ZTEST"."SALGRADE" 5 rows
. . imported "ZTEST"."T1" 5 rows
. . imported "ZTEST"."T2" 5 rows
. . imported "ZTEST"."T3" 5 rows
. . imported "ZTEST"."BONUS" 0 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
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/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 21 error(s) at Fri Feb 3 16:42:41 2017 elapsed 0 00:00:19
ZTEST@book> select * from tab ;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
DEPTX TABLE
EMP TABLE
LOCK_OBJECT_RECORD TABLE
SALGRADE TABLE
SESSION_WAIT_RECORD TABLE
T1 TABLE
T2 TABLE
T3 TABLE
10 rows selected.
--//OK,已经完成了克隆.
--不建立用户ztest,重复测试看看.
$ impdp system/oracle directory=DATA_PUMP_DIR network_link=loopback REMAP_SCHEMA=scott:ztest REMAP_TABLESPACE=users:users SCHEMAS=scott
Import: Release 11.2.0.4.0 - Production on Fri Feb 3 16:46:20 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/a** directory=DATA_PUMP_DIR network_link=loopback REMAP_SCHEMA=scott:ztest REMAP_TABLESPACE=users:users SCHEMAS=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.375 MB
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
. . imported "ZTEST"."SESSION_WAIT_RECORD" 8122 rows
. . imported "ZTEST"."LOCK_OBJECT_RECORD" 8122 rows
. . imported "ZTEST"."DEPT" 4 rows
. . imported "ZTEST"."DEPTX" 4 rows
. . imported "ZTEST"."EMP" 14 rows
. . imported "ZTEST"."SALGRADE" 5 rows
. . imported "ZTEST"."T1" 5 rows
. . imported "ZTEST"."T2" 5 rows
. . imported "ZTEST"."T3" 5 rows
. . imported "ZTEST"."BONUS" 0 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
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/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Fri Feb 3 16:46:37 2017 elapsed 0 00:00:15
--//这样ztest用户的口令与scott的口令一样.
