Home » Infrastructure » Windows » how to put current date and time in dump file using expdp utility in windows (oracle 10g, 10.2.0.4.0 , windows server 2008 enterprise edition )
how to put current date and time in dump file using expdp utility in windows [message #610530] Fri, 21 March 2014 01:39 Go to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
hello i want to ask u in my database i have one user name scott1 i want to take this user logical backup everyday using expdp. i know how to take schemas backup using expdp i take this user backup using this

expdp system/moon@orcl directory=practice dumpfile= scott1_03192014.dmp logfile=scott1_03192014.log schemas=scott1


but i want to run this script everyday can anybody suggest me how i put current date and time into my .dmp file and .log file

can you suggest me any script for windows

Re: how to put current date and time in dump file using expdp utility in windows [message #610533 is a reply to message #610530] Fri, 21 March 2014 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here's a hint:
E:\>echo %date%
21/03/2014

Re: how to put current date and time in dump file using expdp utility in windows [message #610538 is a reply to message #610533] Fri, 21 March 2014 02:24 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yet another one, which shows how to extract certain parts from the %date% (note that mine differs from Michel's, and yours might be different from ours).
M:\>echo %date%
pet 21.03.2014.

M:\>echo exp_file_%date:~10,4%_%date:~7,2%_%date:~4,2%.dmp
exp_file_2014_03_21.dmp

M:\>
Re: how to put current date and time in dump file using expdp utility in windows [message #610541 is a reply to message #610533] Fri, 21 March 2014 02:32 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
thanks for your reply michel but michel i do not know how to use this but i try it


expdp system/moon@orcl schemas=scott directory=practice dumpfil
e=scott echo %date%.dmp logfile=scott.log


but its create 4 file there into my practice directory named

1) scott.log size 2 kb 2) SCOTT.DMP size 236 kb 3) ECHO.DMP size 4 kb 4) 21-03-2014.DMP size 4 kb
Re: how to put current date and time in dump file using expdp utility in windows [message #610543 is a reply to message #610541] Fri, 21 March 2014 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to format the date and Littlefoot showed you how to do it and he also pointed that date format depends on your country so you have to adapt the formula for your date format.
For mine:
E:\>echo %date%
21/03/2014

E:\>echo %date:~6,4%
2014

E:\>echo %date:~3,2%
03

E:\>echo %date:~0,2%
21

E:\>echo %date:~6,4%%date:~3,2%%date:~0,2%
20140321

[Updated on: Fri, 21 March 2014 02:45]

Report message to a moderator

Re: how to put current date and time in dump file using expdp utility in windows [message #610547 is a reply to message #610541] Fri, 21 March 2014 02:55 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
thanks for you reply lie to me

i try this your syntax

expdp system/moon@orcl schemas=scott directory=exdp dumpfil
e= echo exp_file_%date:~10,4%_%date:~7,2%_%date:~4,2%.dmp exp_file_2014_03_21.dm
p


but its gives me error
error is

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39124: dump file name "exp_file_%dd%_mm_y%.dmp" contains an invalid substitu
tion variable




Re: how to put current date and time in dump file using expdp utility in windows [message #610548 is a reply to message #610547] Fri, 21 March 2014 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As we said: date may not be in the same format for you than for Littlefoot or I.
You have to do the same exercise than I did.
Do it and post the result.

Re: how to put current date and time in dump file using expdp utility in windows [message #610549 is a reply to message #610547] Fri, 21 March 2014 03:02 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ECHO was here just to show you the output; you have to remove it when exporting data. Once again, this format might not be what you are after (see again what Michel said).

expdp system/moon@orcl schemas=scott directory=exdp dumpfile=exp_file_%date:~10,4%_%date:~7,2%_%date:~4,2%.dmp
Re: how to put current date and time in dump file using expdp utility in windows [message #610550 is a reply to message #610549] Fri, 21 March 2014 03:19 Go to previous messageGo to next message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
thanks again both of you
i did this


C:\>echo %date%
21-03-2014

C:\>echo %date:~6,4%
2014

C:\>echo %date:~3,2%
03

C:\>echo %date:~0,2%
21

C:\>echo %date:~6,4%%date:~3,2%%date:~0,2%
20140321


C:\>expdp system/moon@orcl schemas=scott directory=exdp dumpfile=exp_file_%
date:~10,4%_%date:~7,2%_%date:~4,2%.dmp



export its successfuly but its create two file in exdp directory

1) export.log size 2 kb 2) EXP_FILE__01_3-.DMP size 236 kb

in .dmp file date not come. and if i run this script everyday its auromaticaly take system date.

[Updated on: Fri, 21 March 2014 03:26]

Report message to a moderator

Re: how to put current date and time in dump file using expdp utility in windows [message #610551 is a reply to message #610550] Fri, 21 March 2014 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post the result of:
echo exp_file_%date:~10,4%_%date:~7,2%_%date:~4,2%.dmp

Copy and paste the first 15 lines of your expdp command execution.

Re: how to put current date and time in dump file using expdp utility in windows [message #610552 is a reply to message #610550] Fri, 21 March 2014 03:32 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This:

2) EXP_FILE__01_3-.DMP size 236 kb

means that you are wasting your and our time, doing something but refuse to THINK.

Why did you bother to discover correct format (echo %date:~6,4%%date:~3,2%%date:~0,2%) and then used (%date:~10,4%_%date:~7,2%_%date:~4,2%) in DMP file name?

[Updated on: Fri, 21 March 2014 03:32]

Report message to a moderator

Re: how to put current date and time in dump file using expdp utility in windows [message #610555 is a reply to message #610552] Fri, 21 March 2014 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Gee! I couldn't even think the file name could be different than the correct formula he found that I didn't verify it. Sad


Re: how to put current date and time in dump file using expdp utility in windows [message #610568 is a reply to message #610555] Fri, 21 March 2014 06:08 Go to previous message
x-oracle
Messages: 380
Registered: April 2011
Location: gujarat
Senior Member
Thanks to both of you i successfuly test it on my tset server i will put this script on my production server

i did this

C:\>echo %date:~6,4%%date:~3,2%%date:~0,2%
20140321

C:\>



expdp system/Moon@sorcl schemas=scott directory=exdp dumpfile=scott%date:~6,4%%date:~3,2%%date:~0,2%.dmp logfile=SCOTT%date:~6,4%%date:~3,2%%date:~0,2%.log


below its my log file

SCOTT20140321.log


;;; 
Export: Release 11.2.0.1.0 - Production on Fri Mar 21 15:58:31 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@sorcl schemas=scott directory=exdp dumpfile=scott20140321.dmp logfile=SCOTT20140321.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  H:\EXDP\SCOTT20140321.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:58:44


and also create dump file SCOTT20140321.DMP and its size its 236 kb ok
Previous Topic: cmd.exe with administrator privileges
Next Topic: Instlling Oracle on top of itself
Goto Forum:
  


Current Time: Fri Mar 29 09:07:59 CDT 2024