Cloning Schema (11g)

Found an interesting way to clone a schema in the same database using datapump with a database link.

Could be considered a highly unsupported method, but it worked for me, so may as well document it.

Here are the steps I followed:

Database: TESTDB

Hostname: TESTHOST

Logged in as my source Schema (HR) and created a Database Link

SQL> connect HR/HR
Connected.
SQL> create database link HRDATA
  2  connect to HR
  3  identified by "HR"
  4  using 'TESTDB.TESTHOST';

Database link created.


Note: HR user has the privileges EXP_FULL_DATABASE and IMP_FULL_DATABASE

Ran Datapump Import Remapping the Schema

impdp HR/HR REMAP_SCHEMA=HR:HR2 network_link=HRDATA

The Output did show some errors, but further investigation reveals that the errors relate to the Master Table which Datapump uses for processing exports/imports. Naturally, being on the same database, there is a conflict in replacing the Master Table and hence these can be ignored.

Import: Release 11.2.0.2.0 - Production on Wed Jul 11 13:48:39 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
Starting "HR"."SYS_IMPORT_SCHEMA_01":  HR/******** REMAP_SCHEMA=HR:HR2 network_link=HRDATA
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 5 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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39129: Object type TABLE: "HR"."SYS_IMPORT_SCHEMA_01"  not imported. Name conflicts with the master table
. . imported "HR2"."TEST"                                262144 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE INDEX "HR2"."SYS_MTABLE_000004F62_IND_1" ON "HR2"."SYS_IMPORT_SCHEMA_01" ("OBJECT_SCHEMA", "OBJECT_NAME", "OBJECT_TYPE") PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARALLEL 1
ORA-39083: Object type INDEX failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE INDEX "HR2"."SYS_MTABLE_000004F62_IND_2" ON "HR2"."SYS_IMPORT_SCHEMA_01" ("BASE_PROCESS_ORDER") PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARALLEL 1
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "HR2"."SYS_IMPORT_SCHEMA_01" ADD UNIQUE ("PROCESS_ORDER", "DUPLICATE") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"  ENABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
ORA-39083: Object type COMMENT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
 COMMENT ON TABLE "HR2"."SYS_IMPORT_SCHEMA_01"  IS 'Data Pump Master Table IMPORT                         SCHEMA                        '
Job "HR"."SYS_IMPORT_SCHEMA_01" completed with 5 error(s) at 13:48:50

Another note for my own reference is that if I wish to place the data and indexes of the target user into a separate tablespace, I only need to add the following syntax to the impdp command:

impdp HR/HR REMAP_SCHEMA=HR:HR2 network_link=HRDATA REMAP_TABLESPACES='HR_DATA:HR2_DATA,HR_INDX:HR2_INDX'