[20170203]克隆schema.txt-白红宇

[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

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,重复测试看看.