Problem solve Get help with specific problems with your technologies, process and projects.

Exporting a table that is constantly updated

How can I export a table that is constantly being updated? I am facing ORA-01555, snapshot too old, when I export for this table though the smallest rollback segment is 400 MB.

This is a common problem.

A "snapshot too old" error occurs when rollback space is used up, and space taken up by committed transactions is reused for new transactions. Reusing space in the rollback segment allows database integrity to be preserved with minimum space requirements, but it imposes a limit on the amount of time that a read-consistent image can be preserved.

If a committed transaction has been overwritten and the information is needed for a read-consistent view of the database, a "snapshot too old" error results. To avoid this error, you should minimize the time taken by a read-consistent export. (Do this by restricting the number of objects exported and, if possible, by reducing the database transaction rate.) Also, make the rollback segment as large as possible.

So, the answer is to create your exports to export only a few objects at a time (instead of the entire database or schema at once) and/or create your rollback segments even larger.

By the way, if you're having this problem consistently, maybe you need to reevaluate your strategy and why you're using export under those conditions in the first place.

For More Information

Dig Deeper on Oracle database export, import and migration

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.