3

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

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Walter Colchado
  • 337
  • 1
  • 6
  • 16
  • 3
    It is always helpful to specify the exact version of Oracle you are using along with the edition (i.e. Oracle 11.2.0.1 Enterprise Edition). This sort of thing changes pretty substantially from release to release and different editions have different functionality. I made some guesses since time is of the essence (many approaches rely on `UNDO` being present which, depending on your configuration, may only give you a window of a few minutes). But the more specific you can be the better. – Justin Cave Mar 08 '13 at 16:46

2 Answers2

13

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.
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 2
    If you also want to **retrieve** the data you deleted, you can use this query with any period of time before you make the mistake : `SELECT * FROM <> AS OF TIMESTAMP TO_DATE('03-OCT-13 08:50:58','DD-MON-YY HH24:MI:SS')`. However, if you get a `ORA-08180:"no snapshot found based on specified time" ` it means Oracle can't retrieve the data stored at that specified time.
     – Walter Colchado Oct 04 '13 at 21:02
1

Starting from Oracle9i R2, doesn't require specific rights

Revert updated columns

update <table> t
    set (<column1>, <column2>, ...) 
    = (select <column1>, <column2>, ... 
        from <table> as of timestamp to_timestamp('2016-07-21 09:39:20', 'YYYY-MM-DD HH:MI:SS') h
        where t.<uk> = h.<uk>);

Revert deleted rows

insert into <table> 
    select * from <table> as of timestamp to_timestamp('2016-07-21 03:30:00', 'YYYY-MM-DD HH:MI:SS')
    where <uk> not in 
        (select t.<uk> from <table> t);

Don't be misleaded with DB timezone and verify current time 

select sysdate from dual;
Mike
  • 20,010
  • 25
  • 97
  • 140