ORA-01555 Using A”S OF TIMESTAMP” Clause

Today called me a person from development team to tell me that needed to recover rows from a table that have been accidentally deleted. The first logic leads you to do is make use of the “flashback query” feature, whe I run the next query I get a ORA-01555, the classic snapshot too old;


SATV*ANTO> select count (*)
 2 from cloud.hco_catalog_longstar as of timestamp to_timestamp('2015-08-17 13:00:00', 'YYYY-MM-DD HH24:MI:SS');
from cloud.hco_catalog_longstar as of timestamp to_timestamp('2015-08-17 13:00:00', 'YYYY-MM-DD HH24
 *
ERROR en línea 2:
ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6$" too small

The problem is that the undo_retention here is very low, an hour. The issue is that I’m asking data from yesterday. If the database had little activity/transactions and deleted rows were few,  maybe the data would be.

The solution is to use the database backup and clone database (this is a version 10g). As a measure could also be looking up the undo_retention, that seems  pretty lame

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s