Example of restoration method after Oracle delete data

Example 1 create a table SQL> create table wdongh (www.2cto.com 2 id integer, 3 name varchar2 (60) 4 ); 2. insert data SQL> insert into wdongh values (1, 'wdh '); 1 rowinserted SQL> insert into wdongh values (2, 'xiaoming '); 1 rowinserted SQL> insert into wdongh values (3, 'hanmei'); www.2cto.com 1 rowinserted SQL> insert into wdongh values (4, 'leilei '); 1 rowinserted SQL> select * from wdongh; ID NAME --------------- ------- 1 wdh 2 xiaoming 3 hanmei 4 leilei 3 delete data SQL> delete from wdongh; 4 rowsdeleted SQL> commit; Commitcomplete SQL> select * from wdongh; id name ----------- 4 obtains the current SCNOracle only performs Restoration Based on SCN. It defines the version submitted by the database at a specific time point. When a transaction is committed, it is assigned a unique SCN that identifies the transaction. The purpose of obtaining the current SCN is to perform a flash-back query attempt. SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER www.2cto.com ---------------------- 668754 SQL> select count (*) from wdongh as of scn 668754; COUNT (*) ---------- 0 5 determine the scn number 5.1 when the delete statement is used to create a temporary table to store the value of scn when deleteSQL> create table temp (count int, scn int) is executed ); tablecreated 5.2 add data SQL> declare 2 iint: = 668700; 3 begin 4 fori in 668700 .. 668754 loop 5 insert into temp (scn) values (I); 6 update temp set count = (selectcount (*) from wdongh as of scn I) where scn = I; 7 endloop; 8 end; 9/PL/SQLprocedure successfully completed www.2cto.com SQL> commit; Commitcomplete 5.3 executes deleteSQL> select * from temp where count> 0 when the number of scn queries is large; count scn ------------ ------------- 4 668700 4 668701 4 668702 4 668703 4 668704 4 668705 4 668706 4 668707 4 8 Rowsselected SQL> select count (*) from wdongh as of scn 668707; COUNT (*) ---------- 4 SQL> select count (*) from wdongh as of scn 668708; www.2cto.com COUNT (*) ---------- 0 we can see that the data is still there when the scn is 668707, that is, the scn is 668708, which is the transaction number of our delete. 6. Restore data SQL> insert into wdongh select * from wdongh as of scn 668707; 4 rowsinserted SQL> select count (*) from wdongh; COUNT (*) ---------- 4 7 kill temporary table tempSQL> drop table temp; www.2cto.com Tabledropped SQL> commit; Commitcomplete author wdh226