Re: snapshot too old error
Date: Tue, 1 Jun 2010 09:39:29 -0700 (PDT)
Message-ID: <facfb314-ad0a-4cda-8f02-f2bf0d31e6b0_at_u3g2000prl.googlegroups.com>
On May 31, 1:43 pm, zigzagdna <zigzag..._at_yahoo.com> wrote:
> I am on Oracle 11.1.0.7 and HP UNIX 11.23i.
> I am doing an expdp and get following error
> expdp system/password directory=expdp_dir dumpfile=expdp.dmp
> logfile=texpdp.log schemas=PQMS PARALLEL=1 exclude=statistics
> compression=all
> ORA-31693: Table data object ""COMPLAINT_LTR_ORIG" failed to load/
> unload and is being skipped due to error:
> ORA-02354: error in exporting/importing data
> ORA-01555: snapshot too old: rollback segment number 6 with name
> "_SYSSMU6_1265054678$"
> My undo size can grow to 8G, and I see it initial size as 2G; so most
> likely rest of undo was not used and increasing undo size will not
> help.
> I have a 24x7 system, and I want to do an export data pump without
> error. How can I overcome this error? Will adding a flashback_time to
> expdp help. Based on my knowledge of exp, it will make things worse as
> for as ORA-1555 is concerned.
There's a bug if you plug in a transportable tablespace and then create a controlfile. If you've done that, see MOS 1066229.1 Updated blocks in the tablespace confuse the issue.
Also, if you want something frightening, copy the table with something like http://oracle-randolf.blogspot.com/2009/04/read-consistency-ora-01555-snapshot-too.html and export that. If the problem is delayed block cleanout, you then perhaps could export the original table.
In the 10g docs it mentions that this can happen with a lot of metadata, google "Data Pump Export and Import consume more undo tablespace than original Export and Import." I would expect it to be fixed in 11g, but... it does bring up the issue of whether the error message is being honest about which object is the cause of the error. The error message is saying the result of the error. Kinda like shooting the messenger - the cause isn't necessarily anything the messenger did wrong.
You might want to let us know your undo parameters.
jg
-- _at_home.com is bogus. http://www.signonsandiego.com/news/2010/jun/01/hewlett-packard-to-cut-9k-jobs-see-1b-in-charges/Received on Tue Jun 01 2010 - 11:39:29 CDT