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

Spoiler: Content

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

Spoiler: 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 :

Spoiler: 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]

 

[Rockalex]

"Hey OP, yeah I've done similar stuff in the past for Chainlink data feeds. Have you tried using the ' chainlink oracle import' script provided by the Chainlink team? It might save you some headache if you're new to this"

 

[ADaqwdas]

Yeah, you can export the Oracle schema using a SQL script with `exp` or `expdp` commands and then load it into the new environment using `imp` or `impdp` for import. For scheduled import/export, you can set up a job using Oracle's DBMS_SCHEDULER package or just schedule a basic UNIX/Linux cron job to run the export/import scripts at intervals.