Timeline for Oracle sql - all records deleted and cannot retrieve data
Current License: CC BY-SA 4.0
6 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 1, 2023 at 12:20 | comment | added | pmdba |
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.
|
|
| Jan 1, 2023 at 11:34 | comment | added | joysticko |
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?
|
|
| Jan 1, 2023 at 11:17 | comment | added | pmdba | 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? | |
| Jan 1, 2023 at 8:15 | comment | added | joysticko |
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
|
|
| Dec 31, 2022 at 19:37 | history | edited | pmdba | CC BY-SA 4.0 |
added 133 characters in body
|
| Dec 31, 2022 at 18:15 | history | answered | pmdba | CC BY-SA 4.0 |