I just wanted to make note of a handy feature that Oracle has: the SELECT … AS OF query. I learned of this feature a couple of months ago when I did a panicked google search after accidentally deleting a lot of live data. I reeeeeeaaaaallllyyy didn’t want to have to call up IT and request a backup from them (not to mention explain to the client why their system was messed up). But with the SELECT…AS OF query, I could grab the data from 5 minutes before my ‘oh sh!t’ moment, re-insert it, and everything’s okay.
Here’s the syntax (so that I can come back and grab it again next time I need it!):
INSERT INTO table_name
(SELECT * FROM table_name AS OF TIMESTAMP
TO_TIMESTAMP(’2010-12-10 15:30:00′, ‘YYYY-MM-DD HH:MI:SS’));
(It seems like, at least on our servers, roughly 12 hours is the farthest back you can go. So if you realize your troubles a day later, you’re SOL.)
