How to restore the data in a Oracle table?

Question

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 1

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.

Answer 2

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;