Oracle Schema export using Datapump API

We wanted to schedule a nightly backup in a new Oracle 19c DB – however, we could not trigger unix script (expdp) from DBMS_SCHEDULER due a linux PAM missing. So, as a temporary arrangement we used DBMS_DATAPUMP API to perform this task as below. DBMS_DATAPUMP API comes handy when you want to perform export / import entirely within SQL Developer and don’t prefer to use the Unix command prompt.

We created a Procedure EXPORT_SCHEMA and scheduled to run daily as below. You can also find the links I referred below to prepare this.

create or replace NONEDITIONABLE procedure SYSTEM.EXPORT_SCHEMA IS
-- Data Pump job handle
h1 NUMBER;
v_current_time DATE := sysdate;
V_DBNAME varchar2(8) := ora_database_name;
v_schema_expression varchar2(100) := 'LIKE ''%MYSCHEMAS''';
v_dump_file_name VARCHAR2 (100);
v_log_file_name VARCHAR2 (100);
-- BEGIN
-- h1 := dbms_datapump.open('EXPORT', 'SCHEMA', NULL, 'EXP_JOB_' | to char(v_current_time, "YYYY_MM_DD_HH24MI'), 'LATEST');
-- v_ dump_file_name := V_DBNAME || 'DAILY_BACKUP' || trim(to_char(v_current_time, 'DAY')) || '%U.dmp;
-- v_log_file_name := V_DBNAME || '_DAILY_BACKUP' || trim(to_char(v_current_time, 'DAY')) || '_' || to_char(v_current_time, 'YYYY-MM-DD-HH24MI') || '.log';
-- dbms_datapump.add_file (handle => h1, filename => v_dump_file_name, directory => 'DATA_PUMP_DIR', fíletype => dbms_datapump.ku$_file_type_dump_file, reusefile => 1);
-- dbms_datapump.add_file (handle => h1, filename > v_log_file_name, directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
-- --A metadata filter is used to specify the schema that will be exported dbms_datapump.metadata_filter(h1, 'SCHEMA_EXPR', v_schema_expression) ;
-- dbms_datapump.set_parallel (h1, 8);
-- dbms_datapump.set_parameter(handle=>h1,name=>'COMPRESSION',value=>'ALL');
-- dbms_datapump.start_job(h1);
-- dbms_datapump.detach(h1); END / grant execute on SYSTEM.EXPORT_SCHEMA TO MYUSER; / begin dbms_scheduler.create_job( job_name=>'EXPORT_DATA_PUMP', job_type=> 'PLSQL_BLOCK', job_action=> 'BEGIN SYSTEM.EXPORT_SCHEMA; END;' , start_date => SYSTIMESTAMP end_date => SYSTIMESTAMP + INTERVAL '3650' day, repeat_interval => 'freq=daily; byday=mon, tue, wed, thu, fri, sat, sun; byhour=20; byminute=0', comments=>'Nightly Backup', enabled=>TRUE, auto_drop=>TRUE); end;

Reference Links:-

https://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_api.htm

https://docs.oracle.com/database/121/SUTIL/GUID-5AAC848B-5A2B-4FD1-97ED-D3A048263118.htm#SUTIL977

https://www.morganslibrary.org/reference/datapump.html

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.