2

Did you know how exactly query for past data works?

The version of oracle is 10G

With this query I can recover some data, but sometimes this query

 select * 
  from table as of timestamp systimestamp - 1

retrieve an error (too old snapshot). Is possible to augment time for this work and retrieve data about 24 hour? Thanks!

Vargan
  • 1,277
  • 1
  • 11
  • 35

2 Answers2

4

The key issue here is the sizing of the undo segments, and the undo retention and guarantee.

The long and short of it is that you need your undo tablespace sized to hold all of the changes that can be made withing the maximum period that you want to flashback over, and you'd want to set the undo retention parameter to that value. If it is really critical to your application that the undo is preserved then set the undo guarantee on the undo tablespace.

Useful docs: http://docs.oracle.com/cd/B12037_01/server.101/b10739/undo.htm#i1008577

Be aware that performance of flashback is rather poor for bulk data, as the required undo blocks need to be found in the tablespace. 11g has better options for high performance flashback.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
2

What the error means is that the rollback segment became invalidated because, usually, the query took too long. There are other causes. Like rollback segment sizing.

How many rows are in the table? - you can get an idea from this select num_rows from all_tables where table_name='MYTABLE_NAME_GOES_HERE';

If there are LOTS of rows, you may need to look at adding some kind index to support your query. Because a full table scan takes too long. If not then it is a DBA issue. Maybe adding an index is a DBA issue in your shop as well.

If this worked well a few days ago, and started happening lately, you probably just passed the threshold for the rollback.

jim mcnamara
  • 16,005
  • 2
  • 34
  • 51
  • 1
    In the context of flashback queries of this sort the error indicates that the undo is not preserved as of the required flashback timestamp, not that the query itself took too long. Mind you, flashback queries are not exactly high performance. Indexes are not likely to be as helpful as usual because they also need to be reconstructed from undo. – David Aldridge Nov 30 '12 at 17:19