Updating table data in Oracle

Question

At work today, I was updating a database table in oracle.

I tried to update a rows with data 6 with 42 for users in certain category.

But, instead, I left out the user and I updated a whole table with with 42 for all users with 6 in "that" column.

I am using pl/sql developer. I clicked yes to post to database.

I know that updates are autocommit command.

I don't know exactly how many rows were affected, but I know there are no more users with 6 in that column and all of the users with the 6 in that column now has 42 in there.

Question

Is there a way I can undo this mistake?

If you committed the changes (probably with autocommit), than you will have to use a backup. If autocommit did not apply, try "rollback" (not sure which rdms you are using) in exactly that session/window in which you executed the update statement. — Stephan Lechner
– Stephan Lechner, Commented Jan 31, 2017 at 22:53
I did try rollback and data did not rollback. I did not backup any tables. I am using oracle 11 with pl/sql developer. Is there an easy way to recover — javaTwilight
– javaTwilight, Commented Jan 31, 2017 at 23:01

Stephan Lechner · Accepted Answer · 2017-01-31 23:36:22Z

2

If you have good luck, you could use something like FLASHBACK TABLE abc TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' minute). Unfortunately, this requires that you had movement enabled before your update statement (which is unlikely that you had).

So I suppose that - in absence of a backup - there is no way to get back the information :-(

But this is just my opinion; I'd suggest to - if possible - leave the DB as is until somebody comes up with better ideas. Before doing anything now - note that operations might truncate logs (which some one else with better ideas would require) - rethink what you are doing.

edited Jan 31, 2017 at 23:36

answered Jan 31, 2017 at 23:18

Stephan Lechner

35.2k4 gold badges38 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

javaTwilight Over a year ago

Thank you, for the information. I cannot do anything until tomorrow morning. I cannot believe I was so stupid. Hope they don't fire me. I hope I will be able to figure out or the guy knows how to turn it back with a script, since these columns are triggered by data in certain columns

javaTwilight Over a year ago

Yeah, I was told without a backup, there is no way to get the information. Thanks for your responses, Stephan Lechner. I am trying to see if I can create a schema of production database, and input the data from the test server into it and then run the production schemas against each other. or something of that matter, as I was told. I ll see.

Collectives™ on Stack Overflow

Updating table data in Oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related