Cloning a schema with a one line command

Posted by

In the world of DevOps, continuous integration and repeatable test cases, the demand for being able to

  • quickly build a suite of database objects,
  • utilise it for a series of tests,
  • then throw the objects away

has become far more common. This is one of the many great use cases for pluggable databases with all of the powerful cloning facilities available. In particular, now that you can take advantage of pluggable databases without* incurring additional license fees, there are some great opportunities there…but that is the topic for another post.

What about if the “unit of work” is not an entire pluggable database. What if we just want to clone just a schema within a pluggable database. Gone are the days where a DBA might be asked to clone a schema once per month, or even once per week. Cloning a schema is now something the developers want to do multiple times per day, as part of an automated process, and not have to involve anyone at all!  Welcome to DevOps! Smile

Unfortunately, we do not yet have a command in the Oracle database which lets you run:

create user SCOTT2 from SCOTT;

so I thought I’d throw something together which hopefully is the next best thing. We can use the PLSQL API into the Datapump feature to facilitate this. Taking advantage of a few concepts, namely:

  • Network based import,
  • The implicit database link associated with a global name,
  • Dynamically alterable external table definitions

we can build a PL/SQL procedure that is our one line clone schema resource for developers.

-- if you want to allow the drop user option, then the 
-- the owning schema will need the following privilege
-- Needless to say, you might want to wrap this within a procedure
-- within its own rights to ensure people don't drop the WRONG user
-- For example:
-- create or replace
-- procedure customised_drop_user(p_user varchar2) is
-- begin
--   if .... then
--      execute immediate 'drop user '||p_user||' cascade';
--   else
--      raise_application_error(-20000,'What the hell?!?!?');
--   end if;
-- end;

grant drop user to MY_USER;

drop table datapump_clone_log;

-- the initial file in the definition (dummy.log) must
-- exist, and the directory you are using (TEMP) must match
-- the declaration in the PLSQL proc which follows
create table datapump_clone_log (
     msg varchar2(4000)
organization external
( type oracle_loader
  default directory TEMP
  access parameters
  ( records delimited by newline
    fields terminated by ','
    missing field values are null
   ( msg )
   location ('dummy.log')
) reject limit unlimited;

-- p_old    = existing schema
-- p_new    = target schema
-- p_drop   = whether we drop the target schema first
-- p_asynch = whether we wait or simply launch the import and return
-- I'd recommend p_asynch as false, because in that way, you'll get the
-- import log returned right back to your screen
create or replace
procedure clone_schema(
              p_old varchar2, 
              p_new varchar2, 
              p_drop_new boolean default true,
              p_asynch boolean default false
              ) is
  l_handle       number;
  l_status       ku$_status; 
  l_state        varchar2(30);
  l_link         varchar2(128);
  l_job_name     varchar2(128) := upper(p_old)||'_SCHEMA_IMP';
  l_log_file     varchar2(128) := lower(p_old)||'_import.log';
  l_default_dir  varchar2(128) := 'TEMP';
  rc             sys_refcursor;
  l_msg          varchar2(4000);
  procedure info(m varchar2,p_dbms_out boolean default false) is
    if p_dbms_out then
    end if;
  if p_drop_new then
      info('Dropping '||p_new,p_dbms_out=>true);
      -- See notes about potentially wrapping this for safety
      execute immediate 'drop user '||p_new||' cascade';
      when others then
        if sqlcode != -1918 then raise; end if;
  end if;
  select global_name into l_link from global_name;
  l_handle :=
    operation   => 'IMPORT',
    job_mode    => 'SCHEMA',
    remote_link => l_link,
    job_name    => l_job_name);

    handle    => l_handle,
    filename  => l_log_file,
    directory => l_default_dir,
    filetype  => dbms_datapump.ku$_file_type_log_file,
    reusefile => 1);

    handle => l_handle,
    name   => 'SCHEMA_EXPR',
    value  => '= '''||p_old||'''');

    handle    => l_handle,
    name      => 'REMAP_SCHEMA',
    old_value => p_old,
    value     => p_new);

  info('Starting job',p_dbms_out=>true);

  if not p_asynch then
          handle    => l_handle,
          mask      => dbms_datapump.ku$_status_job_status,
          job_state => l_state,
          status    => l_status);
        when others then
          if sqlcode = -31626 then
             l_state := 'COMPLETED';
          end if;
      exit when (l_state = 'COMPLETED') or (l_state = 'STOPPED');
    end loop;
    info('Final state:'||l_state,p_dbms_out=>true);
  end if;

  if not p_asynch then
    open rc for 'select msg from datapump_clone_log external modify ( location ( '''||l_log_file||''' ) )';
      fetch rc into l_msg;
      exit when rc%notfound;
    end loop;
    close rc;
  end if;
sho err

You can also get the source from my repo here.

Now lets have a look at the routine in action. This from my 18c database.

SQL> set serverout on
SQL> exec clone_schema('SCOTT','SCOTT2');
172055:Dropping SCOTT2
172057:Starting job
172232:Final state:COMPLETED
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 184.1 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/TABLE/TABLE
. . imported "SCOTT2"."BIGT"                            1146660 rows
. . imported "SCOTT2"."DEPT"                                  4 rows
. . imported "SCOTT2"."EMP"                                  14 rows
. . imported "SCOTT2"."SALGRADE"                              5 rows
. . imported "SCOTT2"."BONUS"                                 0 rows
ORA-39082: Object type PROCEDURE:"SCOTT2"."BLAH2" created with compilation warnings
ORA-39082: Object type PROCEDURE:"SCOTT2"."BLAH" created with compilation warnings
Job "MCDONAC"."SCOTT_SCHEMA_IMP" completed with 2 error(s) at Wed Nov 6 17:21:29 2019 elapsed 0 00:00:33

PL/SQL procedure successfully completed.

Note: If you want to run this on a version of the database below 18c, you can simply break the dynamic external table alteration into an ALTER statement to change the location, and then just query the external table as per normal. All of the rest of the code should work without alteration.


* To see the details about the new license free options for pluggable databases, check out my video below:


  1. Hi, thank you for sharing such a great tip. Please advise on what you mean by “If you want to run this on a version of the database below 18c, you can simply break the dynamic external table alteration into
    an ALTER statement to change the location”

  2. In 12.2 you will probably be ok, but before that you cannot modify the location at *query* time, so you would need to do:

    alter table datapump_clone_log location (‘my_import_log’);
    open rc for select * from datapump_clone_log

    I’ve added a new version to handle this at my git repo

  3. Is there a way to include the OBJECT grants with the schema import ? I am trying (from 12.2) to import a schema from and the schema import is fine, EXCEPT the grants for objects outside the user’s schema which are *not* copied.

  4. Hi, thank you very much for sharing!

    Because I’m working with dbms_datapump the last days and trying out your “one line schema coding” today, I ran into the following issues with it. I think they’re worth to mention here and I hope this is okay!

    Error ‘PLS-00201: identifier ‘DBMS_LOCK’ must be declared
    is raised when running the script to create the clone_schema procedure, because there is no public access for that SYS package. Access must be manually granted (11g, 12c, 18xe and 19c)

    On 12c, 18xe, 19c I need to explicit grant “DROP USER” to the exporting user.

    Executing the clone_schema procedure raises: ORA-31626: job does not exist on 11g, 12c, 18xe and 19c.
    As you mentioned 2017 on Ask Tom, an explicit GRANT CREATE TABLE to the export user is also necessary.

    After that, the export works but with some errors:

    First I get the following, if a schema contains types:
    ORA-39083: Object type TYPE_NAME failed to create with error:
    ORA-02304: invalid object identifier literal while doing impdp

    This can be corrected by adding:
    dbms_datapump.metadata_transform (
    handle => l_handle,
    name => ‘OID’,
    value => 0 );
    to the code.

    I’m ashamed of, but the schema I’ve tested contains tables with LONG columns (our developers still life in the nineties).
    Cause of: ORA-31679 is received when using DataPump import over network link to import a table that has a LONG column
    I see no solution other then using a dump file for cloning this tables.

    Last I get ORA-00001: Unique Constraint (SYS.I_JOB_JOB) violated, because the tested schema contains a database job also. I have to manually create the job in the target schema.

    That’s it. I hope this will help someone coming along.

    Again thanks for all the input you give with your blog and on Ask Tom!

      1. Connor, thanks for this script. Question: did you experience any of the errors that Maurice mentioned? Maybe not the LONG type errors but the others? For example, having to use dbms_datapump.metadata_transform to correct ORA-39083 when an object type exists? Is this a known bug or is something else going on?

  5. Connor,

    what if the schema got spread across a set of (dedicated) tablespaces – with no other schema having objects on it – then the create pluggable database using “user_tablespaces” clause wont work here?


Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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