Oracle – Retrieve Deleted Rows

Smart Panda - Database

Smart Panda - DatabaseRetrieve Deleted Rows from Oracle Database 12c

Okay so last night I went to clear a table of historical data except for the last 7 days.  When I ran the command, it deleted the rows and I committed the transaction and then realized that I deleted the last 7 days and left the other 360 days worth that I had wanted to delete.  Greater Than vs Less Than, lesson there is to not write code on the fly when your tired at 2am in the morning.

I have flashback turned on and archive logging, but I found a neat little SQL method to get my missing rows back:

Insert into TABLE_I_DELETED_ROWS_FROM
(SELECT * FROM TABLE_I_DELETED_ROWS_FROM AS OF TIMESTAMP TO_TIMESTAMP('2014-07-06 10:45:10 PM','YYYY-MM-DD HH:MI:SS PM')
WHERE MYDATEFIELD > TO_DATE('2016-06-29','YYYY-MM-DD'))

I executed this at about 10:55 pm, and it put the 10,000 rows I deleted back into my system that were there at between 06-29 and 07-06.

Nice and easy.