Here is one quick example how to clone one schema within a Oracle database. The most easiest way is to use data pump. My hint will have also the benefit that we won’t need any temporary space. I’m using database version 11.2 and I saw no new feature in version 12.1 which could made it easier to clone schemas.
Preparation
Let’s begin with creating a OS directory for data pump. Be aware that we only store log files into not the whole schema.
[oracle@server orcl]$ mkdir oradp [oracle@server orcl]$ ls -ltr total 254192 drwxr-x--- 2 oracle oinstall 4096 Feb 4 2010 dpdump drwxr-x--- 2 oracle oinstall 36864 Apr 18 15:11 adump drwxr-xr-x 2 oracle oinstall 4096 Apr 18 15:16 oradp
Create a directory object in the database which points to the former new OS directory.
SQL> create directory ORADB_DIR AS '/u01/app/oracle/admin/orcl/oradp'; Directory created.
In addition we’re creating a database link to itself. So a loopback adaptar you can say.
SQL> create public database link ORADB_LINK connect to SYSTEM identified by oracle using 'ORCL'; Database link created.
Test the new database link
SQL> select * 2 from dual@ORADB_LINK; D - X
Executing
Now the magic begins. Use the import utilitly from data pump “IMPDP” and add important parameters. I have one source user called TESTER1 and one target user called TESTER3
- DIRECTORY= Directory for the log files
- NETWORK_LINK= Target database where we want to copy the schema, in this case it’s the same database
- REMAP_SCHEMA= <source schema> : <target schema>
- EXCLUDE= If target user already exists then add this clause, otherwise user will be newly created
[oracle@server trace]$ impdp system/oracle schemas=TESTER1 DIRECTORY=ORADB_DIR NETWORK_LINK=ORADB_LINK REMAP_SCHEMA=TESTER1:TESTER3 EXCLUDE=USER LOGFILE=ORADB_DIR:datapump.log Import: Release 11.2.0.1.0 - Production on Mon Apr 18 15:29:42 2016 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** schemas=TESTER1 DIRECTORY=ORADB_DIR NETWORK_LINK=ORADB_LINK REMAP_SCHEMA=TESTER1:TESTER3 LOGFILE=ORADB_DIR:datapump.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 7.125 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/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE . . imported "TESTER3"."TABELLE3" 55634 rows . . imported "TESTER3"."TABELLE1" 1 rows . . imported "TESTER3"."TABELLE2" 1 rows Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 0 error(s) at 15:30:26
Double check if new user TESTER3 has the same database objects like TESTER1
SQL> select object_name 2 from dba_objects 3 where owner = 'TESTER1'; OBJECT_NAME -------------------------------------------------------------------------------------------------------------------------------- TABELLE1 TABELLE2 TABELLE3 SQL> select object_name 2 from dba_objects 3 where owner = 'TESTER3'; OBJECT_NAME -------------------------------------------------------------------------------------------------------------------------------- TABELLE1 TABELLE2 TABELLE3
