0

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

2 Answers 2

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.

1
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Mar 5, 2023 at 18:54
0

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;
4
  • Of course I used select * from matches; query but it didn't return any result. The only option to display results was running query from the post. But now, I am not even able to run this query due to 01555. 00000 - "snapshot too old: rollback segment number %s with name \"%s\" too small" *Cause: rollback records needed by a reader for consistent read are overwritten by other writers *Action: If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments I tried to restore system to 2 days ago, but didn't help as well
    – joysticko
    Jan 1, 2023 at 8:15
  • A more detailed explanation of what state your system was in, how the data was inserted, what worked at that point, and a timeline of everything you've tried to recover the data would be helpful. Was this data present/available for a long time already, or was it just inserted and then disappeared right away? What sort of backup/restore was attempted? Why didn't that work? Do you have any auditing enabled? Exactly what other commands (that you know of) were run against your tables? What kind of environment are you working in (dev, test, prod) and what version of Oracle?
    – pmdba
    Jan 1, 2023 at 11:17
  • It happened for me for the first time and I have saved records to database since ~ 1 year. The only commands I use only SELECT, INSERT, UPDATE. Few days ago I used database and everything was fine, but the next day all records were removed. I use the database only for my own purposes. I am just looking for solution to retrieve data from the past - I tried something like insert into matches (select * from matches as of timestamp systimestamp - interval '24' hour) but it didn't work. I use Oracle SQL developer environment. Is maybe any way in this tool to restore data?
    – joysticko
    Jan 1, 2023 at 11:34
  • Without more detail, the only thing I can question is whether you finalized the missing transactions with the COMMIT command. If not, then they may have been rolled back - effectively undone - when you logged out. If they were committed, then at this point there likely isn't anything you can do without a good RMAN backup to restore from and unless you're willing to lose the data you've added since then. The ORA-01555 error indicates that you're already past the point where a flashback query is possible, assuming the inserts were committed correctly in the first place.
    – pmdba
    Jan 1, 2023 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.