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');
--
--exclude jobs
DBMS_DATAPUMP.METADATA_FILTER(l_job_handle,
'EXCLUDE_PATH_EXPR',
'IN (''JOB'')');
--
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;

The above job will have 7 backup files being used that have weekday names in the file name and recycled / reused every week. If you cannot accommodate 7 backup files, you can use the following.

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_num_dumps_to_retain number :=4;
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')) 
mod(to_number(trim(to_char(v_current_time,'J'))),v_num_dumps_to_retain) 
|| '%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');
--
--exclude jobs
DBMS_DATAPUMP.METADATA_FILTER(l_job_handle,
'EXCLUDE_PATH_EXPR',
'IN (''JOB'')');
--
dbms_datapump.start_job(h1);
--
dbms_datapump.detach(h1);
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