Re: Ora-01555 error from a consistent export

Hi Peter,

the export is simply doing a select. And when it moves from table to table it uses the same SCN ("snapshot" of the database) as it did when starting to export the first table. A select will never cause the rollback segment to extend. Only DML transactions do. So it seems likely that other transactions were actively doing DML during the time your export was running.The ORA-1555 simply means that there was so much DML (ie changes) going on in the database that that rollback segement start chewing its own tail while in search of free blocks to keep the undo of the DML.

Things that can help to prevent this "snapshot too old) error: - prevent much dml from happening (by shouting around, hey I am doing this full export now, so be quiet ! :-)
- optimize the export so that it will take less time to complete (direct=y, buffer=10000000 etc)
- reduce the chances of losing undo that the export needs by creating multiple larger rollback segments

Regards,
Andre

2007/7/10, Schauss, Peter <peter.schauss_at_ngc.com>:
>
> (Oracle 8.1.7.4/SunOs 5.9)
>
> A full export (consistent=y) from time to time gives me an Ora-01555. I
> have read two documents in Metalink(10630.1 and 1011108.6), both of
> which seem to be telling me to use a larger rollback segmnet. Now I
> have two questions:
>
> 1. Why doesn't the rollback segment grow when it runs out of space?
> The segment listed in the error message currently has only 12 extents
> when the maximum number was set to 32765. The db_block size is 16k
> which, based on Metalink doc 1011108.6, should limit the number of
> extents to about 1000. The rollback tablespace has about 1 gb of free
> space. All of this would seem to suggest that the rollback segment
> shown in the error message had room to grow.
>
> 2. Assuming that I need to assign a specific, large rollback segment to
> my export job, how do I do that?
>
> Thanks,
> Peter Schauss
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l