Oracle sql - all records deleted and cannot retrieve data

Question

All records from all of my tables were removed from database. I didn't use any DELETE method to clear the database. Have you got any idea what could happen? I have never had similar situation before. I tried to retrieve data by following command

  select * from matches as of timestamp systimestamp - interval '24' hour;

but I get error: 01555. 00000 - "snapshot too old: rollback segment number %s with name "%s" too small"

I only add/update records and have no idea how to retrieve data from my tables.

Can someone guide me? Thanks a lot

Answer 1

ORA1555 might be caused by another process in the database. The root cause is often that someone commits inside of a pl/SQL loop. It is a classic mistake to commit every 1000 rows or do while processing a large table

Tom Kyte of Oracle database fame describes the problem in this AskTom Question clearly The main point is:

An ORA-1555 is never about running out of rollback. It is about rollback that was generated being overwritten. A select statement will not cause rollback to be "held". As soon as the transaction that generated the rollback commits - that rollback may be reused and if it is and it is needed by some query, you will get an ORA-1555.

Answer 2

Your data was almost certainly not deleted. Don't use as of in your query. The system is trying to retrieve data as it existed at that moment in time systimestamp - interval '24', which might even be before the inserts, and which you no longer have enough redo to do, regardless. Just use:

select * from matches;