| datapump export substitution variables [message #270689] |
Thu, 27 September 2007 15:02  |
PLSQLPROB
Messages: 11
Registered: May 2003
|
Junior Member |
|
|
|
Is there a way to add a date to the dump filename as a substitution variable? I know there is a variable %U for adding 01, 02, 03 ... like dumpfile_01.dmp but instead I want to have a file with a date like dumpfile_07272007.dmp using datapump export. Thanks and appreciate for your help.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: datapump export substitution variables [message #271203 is a reply to message #271201] |
Sun, 30 September 2007 15:44  |
seethem
Messages: 41
Registered: September 2007
|
Member |
|
|
Well this is an example of a pl/sql script that would take a full database export...immediately
declare
h1 NUMBER;
begin
h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'FULL', job_name => 'TESTING', version => 'COMPATIBLE');
dbms_datapump.set_parallel(handle => h1, degree => 1);
dbms_datapump.add_file(handle => h1, filename => 'EXPDAT.LOG', directory => 'DP_NODE2', filetype => 3);
dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
dbms_datapump.add_file(handle => h1, filename => 'EXPDAT%U%D.DMP', directory => 'DP_NODE2', 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;
/
|
|
|
|
|
|
|
|
| Re: datapump export substitution variables [message #271263 is a reply to message #270689] |
Mon, 01 October 2007 01:28 |
PLSQLPROB
Messages: 11
Registered: May 2003
|
Junior Member |
|
|
|
Thanks and appreciate for everyone's input. My OS is Windows server 2003 and I want to save the daily exports on a daily basis and trying to save based on the date, the export was taken using the batch program. Did the PLSQL program work for you? Is there a simple way other than the PLSQL program?
|
|
|
|
| Re: datapump export substitution variables [message #271266 is a reply to message #271263] |
Mon, 01 October 2007 01:47 |
 |
Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I guess Windows 2003 support DOS batch files. If so, check this code and see if you can adjust it accordingly to your needs.rem test.bat
@echo off
setlocal
for /f "tokens=2" %%i in ("%date:/=_%") do set vdate=%%i
for /f "tokens=1,2 delims=:" %%i in ("%time: =%") do set vdate=%vdate%_%%i%%j
c:\oracle\product\10.2.0\db_1\bin\exp.exe userid=scott/tiger@orcl file=e:\syr%vdate%.dmp owner=scott
endlocal
echo on
When executed:
c:\> test.bat
c:\oracle\product\10.2.0\db_1\bin\exp.exe userid=scott/tiger@orcl file=e:\syr07_19_2006_803.dmp owner=scott
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
