How to restore the data in a Oracle table?

oracle oracle10g flashback
Walter Colchado picture Walter Colchado · Mar 8, 2013 · Viewed 15.9k times · Source

I did a terrible mistake on my work, I executed an updated query on a oracle table without the 'where' clause and everything changed on this table, I was wondering if there is any way to restore the data on a table. I know I can use Flashback, but Is there another way to do that? If you know how to make a flashback table in oracle, please let me know.

I'm using Oracle 10g R2 10.2.0.1

Answer

Justin Cave picture Justin Cave · Mar 8, 2013

First, did you commit the change? If not, you can simply issue a rollback to revert your changes.

Assuming that you did commit your changes, are other users modifying the table at the same time? Do you need to preserve the changes that others have made and only revert the changes you made in your transaction? Or can you restore the entire table to a point in time before your changes were made?

If you can restore the entire table to a point in time

FLASHBACK TABLE <<table name>>
  TO TIMESTAMP( systimestamp - interval '10' minute )

will return a table to the state it was in 10 minutes ago assuming that the UNDO necessary to do so remains available (so you only have a limited time after making a mistake to be able to flashback that mistake). In order to issue a FLASHBACK TABLE, you also have to make sure that

  • The table has enabled row movement ALTER TABLE <<table name>> ENABLE ROW MOVEMENT
  • You must have FLASHBACK privileges on the table or the FLASHBACK ANY TABLE system privilege.

Related questions

How to find the privileges and roles granted to a user in Oracle?

I am using Linux, Oracle10g. I have created one user called test. and granted create session and select any dictionary permission to the same user. i also granted sysdba and sysoper roles to the same users. Now i want …

Read More
Checking oracle sid and database name

I want to check SID and current database name. I am using following query for checking oracle SID select instance from v$thread; but table or view does not exist error is coming. I am using following query for checking …

Read More
CASE .. WHEN expression in Oracle SQL

I have the table with 1 column and has following data Status a1 i t a2 a3 I want to display the following result in my select query Status| STATUSTEXT a1 | Active i | Inactive t | Terminated a2 | Active a3 | Active One …

Read More