Re: Exporting a large table

ORA-01555 snapshot too old does not occur only if the table being exported is being updated during the export. Other transactions making updates could also result in ORA-01555.
Your export is taking 3.5 hrs and it is considered a large query. So any transactions that begin after your export started need to store the old values even after the transactions are commited. And this is your problem. If your export takes 4 hours, the rollback segment should be large enough to hold 4 hours worth of changes.
If it is not convincing enough, read how ORACLE maintains read-consistency. That is the reason long running queries are supposed to be run when DML activities are low. A transaction should see data in the database as it existed at the time the transaction started, even if the transactions that began after the first transaction are commited. You have 2 choices to overcome the problem. 1. Have large rollback segments, big enough to hold 4 hours worth of updates. 2. Shutdown, startup, alter system enable restricted session. Findout the database users who have restricted session privilege and inform them to postpone DML intensive transactions, till the export is complete.

For both the above options, you may want to consider exporting in DIRECT=Y mode, which will finish the export much quicker.

Hope this helps.

Please let me know if this solution helped by e-mail.

Good luck,
CM

In article <352DED90.34A1_at_sea.ericsson.se>,   Guenter Radakovits <guenter.radakovits_at_sea.ericsson.se> wrote:
>
> Hitesh wrote:
> >
> > I am getting the following oracle error message during an
> > export of a very large table which is not being updated or
> > queried during the process. (The table has used up about
> > 2 gig of database space and I think it has 10 million+ rows)
> >
> > The process lasted for 3.5 hrs and then I got the error
> > message.
> >
> > EXP-00008: ORACLE error 1555 encountered
> > ORA-01555: snapshot too old (rollback segment too small)
> >
> > Any ideas how to dump this table?
> >
> > Thanks in advance.
> > Hitesh Patel
>
> Hi,
>
> It looks like the table was updated, deleted or inserted during the
> export.
>
> Make shure that nobody is modifying the table and try the export again.
> (revoke update, insert, delete ....)
>
> good luck
>
> guenter
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri Apr 10 1998 - 12:52:15 CDT