OraFAQ Forum: Windows » how to put current date and time in dump file using expdp utility in windows

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 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to put current date and time in dump file using expdp utility in windows [message #610530]

Fri, 21 March 2014 01:39

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

Report message to a moderator

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

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Here's a hint:

E:\>echo %date%
21/03/2014

Report message to a moderator

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

Littlefoot
Messages: 21823
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:\>

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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

Littlefoot
Messages: 21823
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

Report message to a moderator

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

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

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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

Littlefoot
Messages: 21823
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

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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

Report message to a moderator

Previous Topic:	cmd.exe with administrator privileges
Next Topic:	Instlling Oracle on top of itself

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 08:36:50 CST 2025