Oracle Database Export with Date Stamping: A Solution

By LKBrwnDBA | Created on 2025-12-16 11:33:36

Written with a informative tone 📝 | Model: keyless-gpt-4o-mini

0:00 / 0:00

Exporting databases can be a complex task, especially when it comes to managing different versions and files. In this guide, we will explore how to export an Oracle database while incorporating date stamping into the file names.

The Problem

Oracle's built-in export tool does not allow for direct integration of date stamps into the filename. This can make it difficult to manage multiple exports and keep track of which files are associated with specific dates.

The Solution

LKBrwnDBA, a knowledgeable member of Oracle forums, provided a work-around for this issue. They created a PL/SQL procedure that opens a data pump job, sets the export parameters, and adds the date stamp to the filename.

Creating the Procedure

To create the procedure, follow these steps:

Select the database from the EM (Enterprise Manager) console.
Go to Maintenance > Export to Export files.
Select any/all export options.
At the end, before submitting, click on "Show PL/SQL".
Copy and paste into an editor.
Edit the procedure to add the date stamp and save it.
Schedule your procedure!

Example Procedure Code

The following is an example of what the modified procedure might look like:

declare
h1   NUMBER;
dt    VARCHAR2(12);
begin
SELECT TO_CHAR(SYSDATE,'YYYYMMDD') INTO dt FROM DUAL;
begin
h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => 'DP_EXPORT_SSU_MONTH_END', version => 'COMPATIBLE');
end;
begin
dbms_datapump.set_parallel(handle => h1, degree => 1);
end;
begin
dbms_datapump.add_file(handle => h1, filename => 'EXPSSU'||dt||'.LOG', directory => 'EXP_DIR', filetype => 3);
end;
begin
dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
end;
begin
dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''SSU'')');
end;
begin
dbms_datapump.add_file(handle => h1, filename => 'EXPSSU'||dt||'.DMP', directory => 'EXP_DIR', filetype => 1);
end;
begin
dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
end;
begin
dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
end;
begin
dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');
end;
begin
dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
end;
begin
dbms_datapump.detach(handle => h1);
end;
end;

Implementing the Solution

To implement this solution, follow these steps:

Create a new job with type 'SQL Script'.
Paste the modified procedure code into the editor.
Edit the procedure to add the date stamp and save it.
Schedule your procedure!

Conclusion

By following these steps, you can create a PL/SQL procedure that opens a data pump job, sets the export parameters, and adds the date stamp to the filename. This will make it easier to manage multiple exports and keep track of which files are associated with specific dates.

Share Your Experience!

Share this post:

Tags:

Back to Blog Posts