I have a schema (
FROM_SCHEMA_A) that I need to clone
on the same database. So, the cloned schema will go by another name, in my case by
TO_SCHEMA_A).
In order to make things a bit more complicated, FROM_SCHEMA_A references objects in another schema (named
SCHEMA_B). This other schema won't be cloned.
Schema definitions
Before starting with the object definitions in the schemas, I create the needed schemas:
create user from_schema_A
identified by p
quota unlimited on users;
grant create procedure,
create session,
create table,
create trigger,
create view
to from_schema_A;
create user schema_B
identified by p
quota unlimited on users;
grant create session,
create table
to schema_B;
Here's the definition for the SCHEMA_B schema. It consisists of one table only:
create table table_b_1 (
a number,
b varchar2(10)
);
Since FROM_SCHEMA_A references this table, it needs some grants:
grant insert, select on table_b_1 to from_schema_A;
Here's the definition for the FROM_SCHEMA_A schema:
create table table_a_1 (
c number,
d varchar2(20)
);
insert into table_a_1 values (10, 'ten' );
insert into table_a_1 values (11, 'eleven');
create view view_a_1 as
select * from schema_b.table_b_1;
create package package_a_1 as
function count_a return number;
function count_b return number;
end package_a_1;
/
create package body package_a_1 as
function count_a return number is
ret number;
begin
select count(*) into ret
from table_a_1;
return ret;
end count_a;
function count_b return number is
ret number;
begin
select count(*) into ret
from view_a_1;
return ret;
end count_b;
end package_a_1;
/
create trigger trigger_a
before insert on table_a_1
for each row
begin
insert into schema_b.table_b_1 values (:new.c, :new.d);
end trigger_a;
/
-- There's a trigger on the table, so the
-- following insersts should fill table_b_1
-- (in schema_b):
insert into table_a_1 values (1, 'one');
insert into table_a_1 values (2, 'two');
Export/Import administrator
In order to perform the export and the import, I create a special
export import administrator:
create user exp_imp_admin
identified by p;
grant exp_full_database,
imp_full_database
to exp_imp_admin;
alter user exp_imp_admin quota unlimited on users;
Performing the export and import
With that user, I am able to export the schema:
connect exp_imp_admin/p
declare
datapump_job number;
job_state varchar2(20);
begin
datapump_job := dbms_datapump.open(
operation => 'EXPORT',
job_mode => 'SCHEMA',
remote_link => null,
job_name => 'Clone schema A, export',
version => 'LATEST',
compression => dbms_datapump.ku$_compress_metadata
);
dbms_output.put_line('datapump_job: ' || datapump_job);
dbms_datapump.metadata_filter(
handle => datapump_job,
name =>'SCHEMA_LIST',
value =>'''FROM_SCHEMA_A'''
);
dbms_datapump.add_file(
handle => datapump_job,
filename => 'clone_schema_a_export.log',
directory => 'DATAPUMP_DIR',
filetype => dbms_datapump.ku$_file_type_log_file);
dbms_datapump.add_file(
handle => datapump_job,
filename => 'clone_schema_a.dmp', -- Note, created will be in UPPERCASE!
directory => 'DATAPUMP_DIR',
filetype => dbms_datapump.ku$_file_type_dump_file);
dbms_datapump.start_job (datapump_job);
dbms_datapump.wait_for_job(datapump_job, job_state);
dbms_output.put_line('Job state: ' || job_state);
dbms_datapump.detach(datapump_job);
exception when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
if datapump_job is not null then
dbms_datapump.detach(datapump_job);
end if;
end;
/
And the following import actually clones the schema. Of particular insterest is the call of dbms_datapump.metadata_remap
declare
datapump_job number;
job_state varchar2(20);
begin
datapump_job := dbms_datapump.open(
operation => 'IMPORT',
job_mode => 'SCHEMA',
remote_link => null,
job_name => 'Clone schema A, import',
version => 'LATEST',
compression => dbms_datapump.ku$_compress_metadata
);
dbms_output.put_line('datapump_job: ' || datapump_job);
dbms_datapump.metadata_remap(
datapump_job,
'REMAP_SCHEMA',
'FROM_SCHEMA_A',
'TO_SCHEMA_A');
dbms_datapump.add_file(
handle => datapump_job,
filename => 'clone_schema_a_import.log',
directory => 'DATAPUMP_DIR',
filetype => dbms_datapump.ku$_file_type_log_file);
dbms_datapump.add_file(
handle => datapump_job,
filename => 'clone_schema_a.dmp', -- Note: export has created the file with UPPERCASE letters
directory => 'DATAPUMP_DIR',
filetype => dbms_datapump.ku$_file_type_dump_file);
dbms_datapump.start_job (datapump_job);
dbms_datapump.wait_for_job(datapump_job, job_state);
dbms_output.put_line('Job state: ' || job_state);
dbms_datapump.detach(datapump_job);
exception when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
if datapump_job is not null then
dbms_datapump.detach(datapump_job);
end if;
end;
/
Links
Creating an (import-) SQL file with DBMS_DATAPUMP
The first table,