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! 
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
begin
dbms_application_info.set_client_info(to_char(sysdate,'hh24miss')||':'||m);
if p_dbms_out then
dbms_output.put_line(to_char(sysdate,'hh24miss')||':'||m);
end if;
end;
BEGIN
if p_drop_new then
begin
info('Dropping '||p_new,p_dbms_out=>true);
--
-- See notes about potentially wrapping this for safety
--
execute immediate 'drop user '||p_new||' cascade';
exception
when others then
if sqlcode != -1918 then raise; end if;
end;
end if;
select global_name into l_link from global_name;
l_handle := dbms_datapump.open(
operation => 'IMPORT',
job_mode => 'SCHEMA',
remote_link => l_link,
job_name => l_job_name);
dbms_datapump.add_file(
handle => l_handle,
filename => l_log_file,
directory => l_default_dir,
filetype => dbms_datapump.ku$_file_type_log_file,
reusefile => 1);
dbms_datapump.metadata_filter(
handle => l_handle,
name => 'SCHEMA_EXPR',
value => '= '''||p_old||'''');
dbms_datapump.metadata_remap(
handle => l_handle,
name => 'REMAP_SCHEMA',
old_value => p_old,
value => p_new);
info('Starting job',p_dbms_out=>true);
dbms_datapump.start_job(l_handle);
if not p_asynch then
loop
begin
dbms_lock.sleep(3);
dbms_datapump.get_status(
handle => l_handle,
mask => dbms_datapump.ku$_status_job_status,
job_state => l_state,
status => l_status);
info('l_state='||l_state);
exception
when others then
if sqlcode = -31626 then
l_state := 'COMPLETED';
else
raise;
end if;
end;
exit when (l_state = 'COMPLETED') or (l_state = 'STOPPED');
end loop;
info('Final state:'||l_state,p_dbms_out=>true);
end if;
dbms_datapump.detach(l_handle);
if not p_asynch then
open rc for 'select msg from datapump_clone_log external modify ( location ( '''||l_log_file||''' ) )';
loop
fetch rc into l_msg;
exit when rc%notfound;
dbms_output.put_line(l_msg);
end loop;
close rc;
end if;
end;
/
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
Starting "MCDONAC"."SCOTT_SCHEMA_IMP":
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
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.
Enjoy!
* To see the details about the new license free options for pluggable databases, check out my video below:
Learning is not a spectator sport 
Leave a reply to Michael Milligan Cancel reply