Uncommitting committed sins
October 10th, 2007 by Peter Scott
I overheard one of my team requesting a DBA to restore a table from backup following an overzealous data deletion incident. I thought the chances of him getting them to take on a single table restore on that database was slim given their current workload and the fact that that the table in question was only used to hold a history of data load batch execution times; it contained no vital information
So I suggested that my developer tried a flashback query to put things to right. A what?
I asked the developer at what time he had his mishap, it was only an hour ago so I suggested that he select the contents of the table as of 2 hours ago into a new table and then fix up his problem when he was confident that we got the right stuff back. A simple:
SELECT * from my_table as of timestamp sysdate - 2/24;
Result one happy developer who has now a new string to his bow.
October 11th, 2007 at 3:33 pm
Let’s hope it’s no a crooked arrow you have given him/her. Have you explained to the developer your reasoning about how long ago the commit was done? And why you did the reasoning in the first place?
I have saved some people’s days in a similar way, but they tended to be less than enthusiastic when then came to me about a delete they did a week ago and I could not help them any more.
In other words, I did not explain the feature properly, with all the undesired side-effects like the same people totally discarding it because it does not work ‘all the time’.
October 11th, 2007 at 6:56 pm
Well, I explained where the information came from how it only has a limited lifespan, how not everything is held, why it was a good job that he did a delete with a slightly incorrect where clause and not a truncate table. I also mentioned the need to check that you are putting the right stuff back