Screwed up table in oracle

Question

I have to admit that I just, well, screwed the pooch on a production database. When running an update query in SQL Developer, I did not realize that only a portion of the query was highlighted. If you have any experience with SQL Developer, this means that SQL Developer will only execute that sub-section of a query -- in this case, apparently, "UPDATE <table> SET <column>=<value>" was highlighted...

This means that the query became, "UPDATE <table> SET <column>=<value>". And yes, commit has been pressed.

Is there any way, besides restoring from backup, that this information can be retrieved, or is it just screwed now?

Answer 1

Was an undo segment created for the database? If so, you can roll back what you just did—if there was enough space in it.

Answer 2

On Oracle you have the magical flashback query if your database is 9i or higher and your undo is not too old. Something like:

SELECT ...
FROM <table> 
AS OF TIMESTAMP TO_TIMESTAMP('12-FEB-2010 10.55.00.000000');

If the query returns ORA-01555 Snapshot Too Old it means that the undo_retention value of your database configuration is not high enough for the recovery...

Answer 3

If you don't have implicit transaction commit turned on and you still have that same connection open you could rollback your last transaction. If not restore from backup is the only other option I can think of.