How to Append Current Date to Export Dump file name 2

[JohnBates]

hi everyone,

This is 10g on Windows.

I want my dump file name to reflect the date it was ran on. I'm using the Job Scheduler tool in Database Control.

So when I try EXPSSU%date.DMP as the dump file name, I get error "dump file name "EXPSSU%d.DMP" contains an invalid substitution variable ."

what is the correct syntax? I dont care about the formatting of the date - whatever the default is will be OK.

Thanks, John

 

[LKBrwnDBA]

I beleve the dump filenames can only contain a substitution variable (%U), which implies that multiple files may be generated.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[JohnBates]

Well, that's a bummer. I wanted to make an export dump at each month-end, keeping all dumps in the same folder. Yes I know about the %U.

Oracle really needs to add more flexibility to their tools.

Thanks,
John

 

[LKBrwnDBA]

There is a work-around:

From EM select database and go to:

1) Maintenence > Export to Export files,
2) Select any/all export options
3) At end, before 'submit' click on "Show PL/SQL"
4) Copy and paste into editor
5) Edit procedure to add the date and save
5) Schedule your procedure!

Your modified procedure would look something like this:

declare
  h1   NUMBER;
  [red]dt   VARCGHAR2(12);[/red]
begin
   [red]SELECT TO_CHAR(SYSDATE,'YYYYMMDD') INTO dt FROM DUAL;[/red]
     h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => '[red]SSU_[/red]EXPORT', version => 'COMPATIBLE');
    dbms_datapump.set_parallel(handle => h1, degree => 1);
    dbms_datapump.add_file(handle => h1, filename => 'EXPSSU[red]'||dt||'[/red].LOG', directory => 'EXP_DIR', filetype => 3);
    dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
    dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''[red]SSU[/red]'')');
    dbms_datapump.add_file(handle => h1, filename => 'EXPSSU'||dt||'.DMP', directory => 'EXP_DIR', 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;
/

PS: Changes to the procedure marked in [red]red[/red].




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[JohnBates]

Now THAT's what I'm talking about. Thanks LWBrwnDBA!

John

 

[SantaMufasa]

Bravo, LK! What a great implementation and illustration. Thanks for posting, and havanuthuh

.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[JohnBates]

I had high hopes that this would work for me.

I tried to edit the code in the existing export job but the code window seems to be view-only.

So I created a new job with type of 'SLQ Script' and the following code:

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 => ''DP_EXPORT_SSU_MonthEnd'', 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 => ''DP_EXPORT_SSU_MonthEnd'', 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;

The Results: job completed in 7 seconds - it didn't generate a dump file. Nothing in the alert log. Should have ran for 15 minutes.

I'm sure I'm doing something wrong but no idea as to what

Thanks, John

 

[LKBrwnDBA]

Does the ''DP_EXPORT_SSU_MonthEnd'' directory exist? Looks like you are using two single quotes.

The directory name (mixed-case or not) should be enclosed in single quotes.

Try to avoid using mixed-case names.





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb