The most popular method to clone a schema in Oracle is impdp tool. It allows to clone one or many schema between databases or inside the same database.

Impdp tool is based on PL/SQL package DBMS_DATAPUMP. This article shows how to use the package and clone a schema using pure PL/SQL.

Prerequisites

To copy a schema you need EXP_FULL_DATABASE privilege to export schema and IMP_FULL_DATABASE to import the schema. A schema can be cloned between databases and inside the same database through database link like in impdp tool.

Following picture depicts following:

• PORA12C1.dbaora.com – oracle database
• TOMASZ – DBA user that clones schema SRC_USER to DST_USER
• LOOPBACK – database link that points to schema TOMASZ
• SRC_USER – source user
• DST_USER – destination user

Clone user inside the same database

Let’s create admin user that will clone users

CREATE USER tomasz IDENTIFIED BY dbaora;
GRANT DBA TO tomasz;
GRANT EXECUTE ON dbms_flashback TO tomasz;

once connected as user TOMASZ

sqlplus> CONNECT tomasz/dbaora@PORA12C1.dbaora.com

create database link that will point to the same user TOMASZ on the same database PORA12C1.dbaora.com

CREATE DATABASE LINK loopback
CONNECT TO tomasz 
IDENTIFIED BY dbaora 
USING 'PORA12C1.dbaora.com';

test database link

SELECT 1 FROM dual@loopback;

         1
----------
         1

then create procedure PRO_COPY_SCHEMA with following parameters

• in_srce_user – source user to that will  be copied
• in_dest_user – destination user that will be created
• in_dest_user_pswd – password for destination user
• in_paral_lvl – parallel level used during clone
• in_netwk_link – source database link
• in_incld_rows – copy data or only metadata

CREATE OR REPLACE PROCEDURE pro_copy_schema 
(
  in_srce_user        IN VARCHAR2,
  in_dest_user        IN VARCHAR2,
  in_dest_user_pswd   IN VARCHAR2,
  in_paral_lvl        IN NUMBER   DEFAULT 8,
  in_netwk_link       IN VARCHAR2 DEFAULT 'loopback',
  in_incld_rows       IN NUMBER   DEFAULT 1 
) 
AUTHID CURRENT_USER
AS
  l_hnd    NUMBER; -- job handle
  l_js     user_datapump_jobs.state%TYPE; -- to hold job status
  l_q      VARCHAR2(1) := chr(39); -- single quote
BEGIN
  /* open a new schema level import job using our loopback DB link */
  l_hnd := dbms_datapump.open ('IMPORT','SCHEMA', in_netwk_link);

  /* set parallel level */
  dbms_datapump.set_parallel(handle => l_hnd, degree => in_paral_lvl);
  
  /* make any data copied consistent with respect to now */
  dbms_datapump.set_parameter
  (l_hnd, 'FLASHBACK_SCN', dbms_flashback.get_system_change_number);
  
  /* restrict to the schema we want to copy */
  dbms_datapump.metadata_filter
  (l_hnd, 'SCHEMA_LIST', l_q || in_srce_user || l_q);
  
  /* remap the importing schema name to the schema we want to create */
  dbms_datapump.metadata_remap
  (l_hnd,'REMAP_SCHEMA',in_srce_user,in_dest_user);
  
  /* copy_data for each table or not 1 - yes 0 - meta data only */
  dbms_datapump.data_filter
  (l_hnd,'INCLUDE_ROWS',in_incld_rows,NULL,NULL);

  /* start the job */
  dbms_datapump.start_job(l_hnd);
  
  /* wait for the job to finish */
  dbms_datapump.wait_for_job(l_hnd, l_js);
  
  /* change the password for new user */
  EXECUTE IMMEDIATE 'ALTER USER ' || in_dest_user || 
                    ' IDENTIFIED BY ' || in_dest_user_pswd;
END;
/

Next step is to create some dummy user with some dummy data. The commands can still be executed as user TOMASZ

CREATE USER src_user IDENTIFIED BY src_user_passwd;
GRANT CONNECT TO src_user;
GRANT CREATE TABLE TO src_user;
ALTER USER src_user QUOTA UNLIMITED ON USERS;
CREATE TABLE src_user.test_tbl AS
SELECT username FROM ALL_USERS;

and finally clone user SRC_USER to DST_USER together with data using procedure PRO_COPY_SCHEMA

BEGIN
  pro_copy_schema 
  (
    in_srce_user      => 'SRC_USER',
    in_dest_user      => 'DST_USER',
    in_dest_user_pswd => 'dst_user_passwd',
    in_paral_lvl      => 8,
    in_netwk_link     => 'LOOPBACK',
    in_incld_rows     => 1 
  );
END;
/

Cloning operation can be monitored using following statement

SELECT * FROM user_datapump_jobs;

JOB_NAME              OPERATION JOB_MODE  STATE      DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
--------------------- --------- --------- ---------- ------ ----------------- -----------------
SYS_IMPORT_SCHEMA_01  IMPORT    SCHEMA    EXECUTING       8                 1                 3

Once completed you can check new user is created together with data

SELECT username 
  FROM dst_user.test_tbl
 ORDER BY 1;

USERNAME
------------------
ANONYMOUS
APEX_040200
APEX_PUBLIC_USER
...
WMSYS
XDB
XS$NULL

Have a fun 🙂

Tomasz