Software/Scripts Oracle import/export schema via SQL script running a JOB

[emailx45]

Oracle import/export schema via SQL script running a JOB
Ivan Revelli - 27/Apr/2020

Спойлер: Content

[SHOWTOGROUPS=4,20]
tested on Oracle XE 10g
to make a simple bk from Oracle you can use dbms library like:

Спойлер: Script to Export Data and MetaData from a Schema

create or replace directory dumpdir as '/home/[youruserpath]/'
/*
EXPORT DATA AND METADATA OF A DIAGRAM
*/
declare
handle number;
begin
handle := dbms_datapump.open('EXPORT','SCHEMA');
dbms_datapump.add_file(handle,'[destination_file_name].dmp','DUMPDIR');
DBMS_DATAPUMP.add_file(
handle => Handle,
filename => '.log',
directory => 'DUMPDIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);
dbms_datapump.metadata_filter(handle,'SCHEMA_EXPR','IN (''[your username for schema]'')');
--dbms_datapump.set_parallel(handle,4);
dbms_datapump.start_job(handle);
dbms_datapump.detach(handle);
dbms_output.put_line('Yes');
end;

to restore the dmp file :

Спойлер: Script to Restore Data and MetaData to Schema

create or replace directory dumpdir as '/home/[youruserpath]/'
grant read,write on directory dumpdir to [your user name for schema];
declare
handle number;
begin
handle := dbms_datapump.open('IMPORT','SCHEMA');
dbms_datapump.add_file(handle,'[source_file_name].dmp','DUMPDIR');
DBMS_DATAPUMP.add_file(
handle => Handle,
filename => '.log',
directory => 'DUMPDIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);
-- dbms_datapump.metadata_filter(handle,'SCHEMA_EXPR','IN (''[your user name for schema]'')');
DBMS_DATAPUMP.SET_PARAMETER(handle,'TABLE_EXISTS_ACTION','REPLACE');
/* EXTRA PARAMS THAT YOU CAN SPECIFY
--dbms_datapump.set_parallel(handle,4);
--DBMS_DATAPUMP.METADATA_REMAP(handle,'REMAP_TABLESPACE','[your parameter]','[your parameter]');
--DBMS_DATAPUMP.METADATA_REMAP(handle,'REMAP_SCHEMA','','');
*/
dbms_datapump.start_job(handle);
dbms_datapump.detach(handle);
dbms_output.put_line('Yes');
end;
[/code]

[/SHOWTOGROUPS]