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'