Oracle Data Retrieval

1,UseDbms_flashbackRetrieve Data
 

You must first useDbms_flashbackPackage power, can be inSysLower Pass

Grant execute on dbms_flashback to user;Authorization

 

SQL> execute dbms_flashback.enable_at_time (date );//Enable snapshot Mode

SQL>In this case, all the tables to be queried areDateYou can open a table through a cursor or store the data in the table to a temporary table.

SQL> execute dbms_flashback.disable ;//End snapshot Mode

SQL>Write the data in the cursor or temporary table back to the original table.

 

 

A sample stored procedure:

1. determine the status before data is deleted:

SQL> connect test/test

SQL> select * from Yang;

SQL> select * from Yang;

Part_id ID name

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

1 1 Yang

1 1 Yang

11 1 ding

SQL> select to_char (sysdate, 'Mm-dd-yy hh24: MI: ss') from dual;

To_char (sysdate ,'

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

01-21-05 14:48:09

2. Delete all data in the table:

SQL> Delete Yang;

SQL> commit;

SQL> select to_char (sysdate, 'Mm-dd-yy hh24: MI: ss') from dual;

To_char (sysdate ,'

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

01-21-05 14:49:08

3. Enable flashback.

SQL> exec dbms_flashback.enable_at_time (to_date ('2017-01-21 14:48:09 ', 'yyyy-mm-dd hh24: MI: ss '));

The PL/SQL process is successfully completed.

SQL> select * from Yang;

Part_id ID name

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

1 1 Yang

1 1 Yang

11 1 ding

This indicates that flashback can be used to retrieve lost data.

Because DML statements cannot be used in the flashback mode, but data can only be viewed. Therefore, data cannot be recovered in the general method. However, we use a cursor in the stored procedure to save data in the flashback state, after flashback is disabled, use this cursor to restore data.

4. The stored procedure is as follows:

SQL> set serveroutput on

SQL> declare

R_yang Yang % rowtype;

Cursor c_yang is select * from Yang;

Begin

Open c_yang;

Dbms_flashback.disable;

Loop

Fetch c_yang into r_yang;

Exit when c_yang % notfound;

Dbms_output.put_line (r_yang.part_id | ',' | r_yang.id | ',' | r_yang.name );

Insert into Yang (part_id, ID, name) values (r_yang.part_id, r_yang.id, r_yang.name );

Commit;

End loop;

Close c_yang;

End;

/

1, 1, Yang

1, 1, Yang

11,1, Ding

The PL/SQL process is successfully completed.

5. Check the data in the table and find that the data has been recovered.

SQL> select * from Yang;

Part_id ID name

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

1 1 Yang

1 1 Yang

11 1 ding

 

 

2. UseTimestampRetrieve Data

 

Insert into Yang

Select * from Yang as of Timestamp to_timestamp ('2017-01-21 ', 'yyyy-mm-dd hh24: MI: ss ');

Insert records of the table Yang at the time point to the Yang table at the current time.