Oracle Flashback

So, I overheard a conversation this week and discovered a really interesting and potentially useful feature called Oracle Flashback. Depending on how your DBA's have setup the database you may or may not be able to hit the ground running with this feature. Let's say we were updating or deleting records in our table and did something we really didn't want to do, such as update or delete the wrong record. Depending on the complexity of your database, you may be able to replace or manually undo the mistake but in an OLTP system more than likely you are pretty much hosed. I've done it. Many have done it. It's often viewed as a badge of courage. You learn to pay closer attention. 

But, let's say you've done something by accident and committed before you noticed your error and you really need to recreate the data the way it was prior to your little whoopsie. Well, in comes Oracle like a messiah - no, a superhero. It saves the day with Oracle Flashback. There is a system package called DBMS_FLASHBACK that you would need execute on from your DBA and they would have to have setup the UNDO_RETENTION and UNDO_MANAGEMENT properties accordingly ahead of time. If not, you are going to have a bad time.

Anyway, here's the scenario: you get tasked with deleting a record from a table called RECIPE. You tried grandma's recipe for potato salad and it tastes like ass and cat food. You want to get rid of that motherfucker before someone ends up dead. In your haste, you forget to qualify your delete statement and up deleting the whole table and you committed. Oh noes!?!?! What do?!?!?! Well, you would write something like:

CREATE TABLE ORACLE_SAVE_MY_ASS_PLS AS 

    SELECT * FROM (

        SELECT * FROM RECIPE

     ) 

     AS OF TIMESTAMP SYSDATE - 0.125;

This will create a table that is a copy of recipe as of 3 hours ago. If your UNDO_RETENTION is set for a day or more, you might be able to fix something you fucked up yesterday. But if you  need to fix something you just did a few moments ago and have made other changes previous to that that were legit, you'd have to alter the number you subtract from SYSDATE to zero in on your change. As a reminder, SYSDATE is the function that returns the time stamp from the database server for when it is run. You can subtract or add a number to SYSDATE to go back or forward in time. Now, obviously for our current purposes we cannot go forward in time, that doesn't make sense. But, often systems will add 30, 60 or 90 days to SYSDATE to set a reminder in the database or to control some future functionality, like password expiration.

So far, I have yet to use this little feature to save my bacon (mmm, bacon!) but I can really see it's usefulness.  A caveat here would still be TRUNCATE. This will not work if you TRUNCATE the table. TRUNCATE does not record the action in the UNDO logs and you will get dry anal raped by TRUNCATE. Use this wisely. Be cautious and use sparingly. You best know what you are about son.

I will leave you hear. I've slacked off a bit lately on posting and I'm trying to get back on the proverbial whore, err, horse or horse-faced whore. Whatever. I need to post more frequently is what I'm trying to say. And I have the sudden urge to watch Sex and the City with the sound off and my pants down. Ah, the Chris Noth is a handsome motherfucker.