Restore data in the delete table

Today, we tested the restoration method for Oracle after deleting table data.

There is a table in the maintenance test of the oracle production system. The data in the table is deleted and commit. The service cannot be interrupted (the database cannot be stopped ), in addition, the system constantly writes data to the table. Now you need to restore the data deleted from the delete operation. You can use scn-based recovery.

Before deleting the database, it is best to query the current scn of the database. The database will be restored based on this scn point.

Example:
Method 1:
1) create a test table;
Create table w as select * FROM dba_objects WHERE 1 = 2;
2) Insert test data into the table;
SQL> insert into w select * from dba_objects;

72558 rows created.

SQL> commit;

Commit complete.
3) SQL> select count (*) from w;

COUNT (*)
----------
72558
4) before deleting the database, obtain the scn of the database to find out when the data exists during recovery;
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
117853514 ------ current scn number
Because the database has not stopped, the scn number is constantly changing. Therefore, you need to find out which scn point the data exists.
SQL> select count (*) from w as of scn 117854000; ------ check whether data exists when scn is 117854000
Select count (*) from w as of scn 117854000
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number ------ it should be that the system has not reached this scn number

SQL> select count (*) from w as of scn 117853000; ------ check whether data exists when scn is 117853000

COUNT (*)
----------
0 ------ nonexistent

SQL> select count (*) from w as of scn 117853500;

COUNT (*)
----------
0

SQL> select count (*) from w as of scn 117853510; no data exists until this scn is found, so it is restored based on this scn point.

COUNT (*)
----------
72558
5) simulate data deletion;
SQL> delete from w;

72558 rows deleted.

SQL> commit;

Commit complete.
SQL> SELECT COUNT (*) FROM w;

COUNT (*)
----------
0
6) The simulation database does not stop, the business is constantly inserting data into the table; [help house http://www.bkjia.com]
SQL> insert into w select * from w as of scn 117844000;

72558 rows created.

SQL> select count (*) from w;

COUNT (*)
----------
72558

1 2 Next Page