By The provided code snippet appears to be a solution to a problem where the Data Pump tool, part of Oracle's database management system, has limitations when it comes to creating export dumps. The limitation mentioned is that file names can only contain a substitution variable (%U), which implies that multiple files may be generated. To overcome this limitation, the code snippet provides a work-around solution that uses single quotes when creating a new directory for the export files. This allows for the use of file names with special characters, such as '%<em>SSU</em>%DATE%.LOG'. Here is a breakdown of the code: 1. `SELECT TO_CHAR(SYSDATE,'YYYYMMDD') INTO dt FROM DUAL;` * This line uses the `TO_CHAR` function to convert the current date and time to a string in the format 'YYYYMMDD'. * The result is stored in the variable `dt`. 2. `h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => 'DP_EXPORT_SSU_MONTH_END', version => 'COMPATIBLE');` * This line opens a new Data Pump job using the `dbms_datapump` package. * The `open` procedure takes several parameters, including the operation type ('EXPORT'), job mode ('SCHEMA'), job name ('DP_EXPORT_SSU_MONTH_END'), and version ('COMPATIBLE'). 3. `execute immediate 'create or replace directory DP_EXPORT_SSU_MONTH_END as ''/u01/exp SSU_MONTH End''';` * This line uses the `execute immediate` statement to create a new directory using the `CREATE OR REPLACE DIRECTORY` statement. * The directory name is specified as `'DP_EXPORT_SSU_MONTH_END'`, and it includes single quotes around the value `' /u01/exp SSU_MONTH End'`. 4. `dbms_datapump.set_parallel(handle => h1, degree => 1);` * This line sets the parallelism level for the Data Pump job using the `set_parallel` procedure. * The `handle` parameter specifies the handle of the job to be modified (`h1`), and the `degree` parameter specifies the degree of parallelism (in this case, 1). 5. `dbms_datapump.add_file(handle => h1, filename => 'EXPSSU'||dt||'.LOG', directory => 'DP_EXPORT_SSU_MONTH_END', filetype => 3);` * This line adds a new file to the Data Pump job using the `add_file` procedure. * The `handle` parameter specifies the handle of the job (`h1`), and the `filename` parameter specifies the name of the file to be added (which includes the date value converted earlier). * The `directory` parameter specifies the directory where the file will be created, which is `'DP_EXPORT_SSU_MONTH_END'`. * The `filetype` parameter specifies the type of file to be added (in this case, 3 for a log file). The rest of the code snippet provides additional settings and options for the Data Pump job, including setting the parallelism level, metadata filtering, data access methods, estimation parameters, and job properties. Overall, the provided solution uses single quotes when creating a new directory for the export files to overcome the limitation mentioned in the problem. | Created on 2025-12-16 22:54:49

Are you struggling with Oracle's Data Pump limitations? You're not alone. In this article, we'll walk you through a step-by-step solution to overcome these limitations and create an export dump at each month-end.

The Problem

Oracle's Data Pump tool has some limitations when it comes to creating export dumps. One of the main issues is that the file names can only contain a substitution variable (%U), which implies that multiple files may be generated.

A Work-Around Solution

However, thanks to the expertise of LKBrwnDBA, we have found a work-around solution. Here's how it works:

Select database and go to: Maintenance > Export to Export files.
Select any/all export options.
At end, before 'submit' click on "Show PL/SQL".
Copy and paste into editor.
Edit procedure to add the date and save.

Here's an example of how your modified procedure would look like: ```sql BEGIN SELECT TO_CHAR(SYSDATE,'YYYYMMDD') INTO dt FROM DUAL;

h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => 'DP_EXPORT_SSU_MONTH_END', version => 'COMPATIBLE');

dbms_datapump.set_parallel(handle => h1, degree => 1);

dbms_datapump.add_file(handle => h1, filename => 'EXPSSU'||dt||'.LOG', directory => 'DP_EXPORT_SSU_MONTH_END', filetype => 3);

dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);

dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''SSU'')');

dbms_datapump.add_file(handle => h1, filename => 'EXPSSU'||dt||'.DMP', directory => 'DP_EXPORT_SSU_MONTH_END', filetype => 1);

dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);

dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');

dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');

dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);

dbms_datapump.detach(handle => h1); END; / ``` Creating a New Job with Single Quotes

To create a new job with single quotes, you can use the following code: ```sql BEGIN SELECT TO_CHAR(SYSDATE,'YYYYMMDD') INTO dt FROM DUAL;

h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => 'DP_EXPORT_SSU_MONTH_END', version => 'COMPATIBLE');

-- Create a new directory for the export files execute immediate ' create or replace directory DP_EXPORT_SSU_MONTH_END as ''/u01/exp SSU_MONTH End''';

dbms_datapump.set_parallel(handle => h1, degree => 1);

dbms_datapump.add_file(handle => h1, filename => 'EXPSSU'||dt||'.LOG', directory => 'DP_EXPORT_SSU_MONTH_END', filetype => 3);

-- Add the date to the file name dbms_datapump.set_parameter(handle => h1, name => 'FILE_NAME_PATTERN', value => '%SSU%DATE%.LOG');

dbms_datapump.add_file(handle => h1, filename => 'EXPSSU'||dt||'.DMP', directory => 'DP_EXPORT_SSU_MONTH_END', filetype => 1);

-- Set the parallelism and job properties dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);

dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''SSU'')');

dbms_datapump.add_file(handle => h1, filename => 'EXPSSU'||dt||'.DMP', directory => 'DP_EXPORT_SSU_MONTH_END', filetype => 1);

dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);

dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');

dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');

dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);

dbms_datapump.detach(handle => h1); END; / ``` Conclusion

By following these steps, you should be able to overcome Oracle's Data Pump limitations and create an export dump at each month-end. Remember to use single quotes when creating a new directory for the export files. Happy exporting!

Here is the rewritten article with a more modern format:

Overcoming Oracle's Data Pump Limitations

Are you struggling with Oracle's Data Pump limitations? You're not alone.

In this article, we'll walk you through a step-by-step solution to overcome these limitations and create an export dump at each month-end.

The Problem

A Work-Around Solution

However, thanks to the expertise of LKBrwnDBA, we have found a work-around solution. Here's how it works:

Select database and go to: Maintenance > Export to Export files.
Select any/all export options.
At end, before 'submit' click on "Show PL/SQL".
Copy and paste into editor.
Edit procedure to add the date and save.