Oracle clone schema using PL/SQL

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-schema1

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

8 thoughts on “Oracle clone schema using PL/SQL

  1. why use imdp+dblink?
    for example:
    impdp system/oracle directory=DATA_PUMP_DIR network_link=loopback REMAP_SCHEMA=scott:ztest REMAP_TABLESPACE=users:users SCHEMAS=scott

  2. Hi Tomasz, Does Oracle data pump over network (db links) use parallel degree? Please check with Oracle documentation. Nice example!

  3. I have two cloned schemas (from 11g to 12c).
    One was created in 2016 and another was created in 2019.
    Can anyone please advise on how to connect to cloned schema (12c) from 11g client?

    I am able to connect to the schema cloned (12c) in 2016 from 11g client. However, I am unable to connect to the schema which was cloned (12c) in 2019. Anyone’s inputs on priority are highly appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.