Home » RDBMS Server » Server Utilities » datapump export substitution variables
datapump export substitution variables [message #270689] Thu, 27 September 2007 15:02 Go to next message
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 #270691 is a reply to message #270689] Thu, 27 September 2007 15:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Is it possible? Yes.
With a subsitution variable? Not one I am aware of.

Regards
Michel
Re: datapump export substitution variables [message #270817 is a reply to message #270691] Fri, 28 September 2007 01:32 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


what is your operating system ??
Re: datapump export substitution variables [message #271135 is a reply to message #270689] Sat, 29 September 2007 22:36 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Hai, whats the wrong with manually add date ?
Re: datapump export substitution variables [message #271136 is a reply to message #270817] Sat, 29 September 2007 22:38 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

gentlebabu wrote on Fri, 28 September 2007 12:32

what is your operating system ??


Data Pump is a utilities. What you will do with OS?
Re: datapump export substitution variables [message #271137 is a reply to message #270689] Sat, 29 September 2007 22:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
use a shell environmental variable & if necessary use inside a "here document".
Re: datapump export substitution variables [message #271199 is a reply to message #270689] Sun, 30 September 2007 15:08 Go to previous messageGo to next message
seethem
Messages: 41
Registered: September 2007
Member
Try %D
Re: datapump export substitution variables [message #271201 is a reply to message #270689] Sun, 30 September 2007 15:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Try %D
please provide an example of a working solution including the answer above.
Re: datapump export substitution variables [message #271203 is a reply to message #271201] Sun, 30 September 2007 15:44 Go to previous messageGo to next message
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 #271225 is a reply to message #271136] Sun, 30 September 2007 23:12 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Arju,

It's possible in shell script or batch file. That is why I'm asking Operating System.

Babu B
Re: datapump export substitution variables [message #271229 is a reply to message #271225] Sun, 30 September 2007 23:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
gentlebabu,
we know it is possible in shell script (and this was my first answer) but this is not the question.

Regards
Michel
Re: datapump export substitution variables [message #271263 is a reply to message #270689] Mon, 01 October 2007 01:28 Go to previous messageGo to next message
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 Go to previous message
Littlefoot
Messages: 21823
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
Previous Topic: Methods to move data between databases
Next Topic: creating listerner manually
Goto Forum:
  


Current Time: Wed Dec 25 09:44:48 CST 2024