Via Lattanzio,23 Milano
+39 02 36592025
info@synaptica.info

Oracle import/export schema via SQL script running a JOB.

Digital solution partner

Oracle import/export schema via SQL script running a JOB.

tested on Oracle XE 10g

to make a simple bk from Oracle you can use dbms library like:

create or replace directory dumpdir as '/home/[youruserpath]/'

/*
ESPORTA DATI E METADATI DI UNO SCHEMA
*/

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 :

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;