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