Oracle 9i using Flash-back query to recover database deletion problem _oracle

If the user mistakenly deletes/updates the data, as the user does not have any direct method to restore, they must help the DBA to restore the database, to the oracle9i, this embarrassing situation has improved. Oracle 9i provides a new technique--a flash-back query that allows users to get data before a bad operation in a timely manner, and to recover for errors without DBA intervention.

Because the hand is cheap, the production data was deleted by me.

With the DELETE statement, and it's quickly returned to the commit.

The following two statements:

ALTER TABLE tablename ENABLE row movement;
Flashback table TableName to timestamp to_timestamp (' 2012-09-13 13:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');

------------------------------------------------------

Remember it's about two o ' clock to run the delete---commit;

The implementation process can be realized from the following sample diagrams;

1. Record of the original table

 $ sqlplus eygle/eygle
sql*plus:release 10.1.0.2.0-production on Wed Mar 08:52:04
(c) 1982, 20 Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition release 10.1.0.2.0-64bit Production
with the partitioning, OLAP and Data Mining options
sql>select count (*) from T1;
  COUNT (*)
----------
    9318

2. Delete all records by mistake

and commit the changes.

Sql>delete from T1;
9318 rows deleted.
sql>commit;
Commit complete.
Sql>select Count (*) from T1;
  COUNT (*)
----------
     0

3. Get current SCN

If you can know exactly what the SCN is best before removing it, you can try the flashback query if you don't know.

Sql>select dbms_flashback.get_system_change_number from dual;
Get_system_change_number
------------------------
         10671006
sql>select Count (*) from T1 as of SCN 10671000;
  COUNT (*)
----------
     0
sql>select Count (*) from T1 as of SCN 10670000;
  COUNT (*)
----------
    9318

We see the data in the scn=10670000.

4. Restore data.

Sql>insert into T1 select * from T1 as of SCN 10670000;
9318 rows created.
sql>commit;
Commit complete.
Sql>select Count (*) from T1;
  COUNT (*)
----------
    9318

Tutorials for other netizens

For database operations, delete must be followed by the where. Today, I accidentally saw a message about the recovery of Oracle mistakenly deleted data, and found that it is really good, the following on my test to report to you.

. SELECT * FROM T_VIRADSL t     //query T_VIRADSL all the data, you can see three of data
 . Delete T_VIRADSL           //Delete all data in T_VIRADSL, three data disappears
 . Select * FROM T_VIRADSL t     //No data.
 INSERT INTO T_VIRADSL SELECT * T_viradsl as of timestamp to_date ('--:: ', ' yyyy-mm-dd hh:mi:ss ')       //deleted data mistakenly Insert Table
 . SELECT * FROM T_VIRADSL t     ///You will see three more data.

Let's analyze the fourth step and pay attention to this sentence:

SELECT * from T_VIRADSL2 as of timestamp to_date (' 2011-01-19 15:28:00 ', ' yyyy-mm-dd hh24:mi:ss ')

What do you mean, find T_VIRADSL2?

At 2011-01-19 15:28:00 all the data for this point in time, since

I found it, you can do whatever you want.