Oracle 9i uses flashback query to Restore database accidental deletion. oracle9i

Oracle 9i uses flashback query to Restore database accidental deletion. oracle9i

If the user accidentally deletes/updates the data, there is no direct way for the user to recover the data, they must seek help from the DBA to recover the database to Oracle9i, this embarrassing situation has improved. Oracle 9i provides a new technical means-Flashback query. You can use flashback query to obtain data before misoperation in a timely manner and perform corresponding recovery measures for errors, all this requires no DBA intervention.

I deleted the production data because of a temporary embarrassment.

The delete statement is used, and commit is quickly returned.

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 the delete --- commit statement that runs around half past two;

The specific execution process can be seen from the following examples;

1. Original table records

 $ sqlplus eygle/eygleSQL*Plus: Release 10.1.0.2.0 - Production on Wed Mar 30 08:52:04 2005Copyright (c) 1982, 2004, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsSQL>select count(*) from t1;  COUNT(*)----------    9318

2. accidentally delete all records

And submit the changes.

SQL>delete from t1;9318 rows deleted.SQL>commit;Commit complete.SQL>select count(*) from t1;  COUNT(*)----------     0

3. Obtain the current SCN

If you know exactly that the SCN is the best before deletion, if you do not know, you can try a flashback query.

SQL>select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------         10671006SQL>select count(*) from t1 as of scn 10671000;  COUNT(*)----------     0SQL>select count(*) from t1 as of scn 10670000;  COUNT(*)----------    9318

We can see that when SCN = 10670000, the data is in.

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 users

For database operations, you must add where after the delete operation ". Today, I accidentally saw a message on the Internet about oracle accidental deletion of data recovery, which is really helpful. Next I will report my test to you.

. Select * from t_viradsl t // query all data in t_viradsl. Three data entries are displayed. delete t_viradsl // delete all data in t_viradsl, and the three data items disappear. select * from t_viradsl t // no data. . Insert into t_viradsl select * from t_viradsl as of timestamp to_Date ('--:', 'yyyy-mm-dd hh: mi: ss') // The accidentally deleted data has been inserted into the table. select * from t_viradsl t // three more data entries are displayed.

Let's analyze the fourth step. 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.

All data at 15:28:00, since

I found it. You can do everything you want.