Tuesday, 30 October 2012

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>expdp sumit/database@DB16UTF_486 tables=test1,test2 directory=EXPORT_DUMP dumpfile=exp_tune6.dmp logfile=exp_tune6.log exclude=statistics flashback_time=SYSTIMESTAMP

Export: Release 10.2.0.1.0 - Production on Tuesday, 30 October, 2012 16:58:52

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39150: bad flashback time
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Solution:

FLASHBACK_TIME is causing the issue here. First step is to check the value of NLS_TIMESTAMP_FORMAT , in my case it's value was  "DD-MON-RR HH.MI.SSXFF AM".
Correct syntax is FLASHBACK_TIME="TO_TIMESTAMP(time-value)".
Because the TO_TIMESTAMP value is enclosed in quotation marks, it would be best to put this
parameter in a parameter file else it causes syntax error on windows dos prompt.

Example:

You can specify the time in any format that the DBMS_FLASHBACK.ENABLE_AT_TIME
procedure accepts. For example, suppose you have a parameter file, flashback.par,
with the following contents:

DIRECTORY= EXPORT_DUMP
TABLES= test1,test2
DUMPFILE=hr_time.dmp
dumpfile=exp_tune6.dmp
logfile=exp_tune6.log
exclude=statistics 
flashback_time="TO_TIMESTAMP(SYSDATE, 'DD-MON-RR HH:MI:SSXFF AM')"

You could then issue the following command:

> expdp sumit/database@db16utf_486  PARFILE=D:\flashback.par

The export operation will be performed with data that is consistent with the SCN that most
closely matches the specified time.




No comments:

Post a Comment