Summary of Oracle Flashback usage

Welcome to the Oracle community forum and interact with 2 million technical staff? 6. data recovery? Disable the table trigger altertriggersphsy. T_INS_LOGIN_TABLEdisable ;? Recover data insertintosphsy. login_tableselect * fromsphsy. login_tableasoftimestampto_timestam

Welcome to the Oracle community forum and interact with 2 million technical staff> enter? 6. data recovery? Disable the trigger alter trigger sphsy. T_INS_LOGIN_TABLE disable ;? Recover data insert into sphsy. login_table select * from sphsy. login_table as of timestamp to_timestam

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

? 6. Data Recovery

? Disable a table trigger

Alter trigger sphsy. T_INS_LOGIN_TABLE disable;

? Restore data

Insert into sphsy. login_table

Select * from sphsy. login_table

As of timestamp to_timestamp ('2017-04-17 17:20:30 ', 'yyyy-MM-DD HH24: MI: ss ')

Where id> 201204171078

And id <201204171141

? Recovery trigger

Alter trigger sphsy. T_INS_LOGIN_TABLE enable;

? 7. Data later than the interval returned 3130 = 3016 + 62 + later data. Interval recovery is deleted by mistake.

Select program, count (*)

From sphsy. login_table

Where a. id> = 201204171078

Group by program;

? D. flashback table restore the table to the previous state

/*

Flashback queries can be used to restore row data of tables accidentally deleted, but other DML statements (insert or update) are incorrectly executed on tables ), you cannot use the flashback query to recover the table to the previous time point. Starting from oracle10g, you can use the flashback table statement to restore the table to the previous time point. By using this feature, you can avoid Incomplete recovery based on time points. Note that to use the flashback table feature on a table, the following conditions must be met:

A. the user must have the flashback any table system permission or the flashback object permission.

B. You must have select insert delete and alter permissions on the table.

C. The initialization parameter undo_retention must be properly set to ensure that the UNDO information is retained for sufficient time.

D. The row moving feature must be activated: alter table table_name enable row movement;

*/

? 1. query the original records. There are 62 rows in the interval.

Select *

From sphsy. login_table

Where a. id> 201204171078

And a. id <201204171141

Order by a. id;

? 2. There are 3074 s later than the interval

Select count (*)

From sphsy. login_table

Where a. id> = 201204171141;

? 3. Delete. First, write down the time point, 17:43:46

Select to_char (sysdate, 'yyyy-MM-DD HH24: MI: ss') from dual;

Delete from sphsy. login_table

Where a. id> 201204171078

And a. id <201204171141

? 4. After deletion, the sphysy. login_table table will be modified, row 3082

Select count (*)

From sphsy. login_table

Where a. id> = 201204171141;

? 5. Activate the row moving feature

Alter table sphsy. login_table enable row movement

? 6. Use the flash back feature to restore the data directly before the deletion time point.

Flashback table sphsy. login_table to timestamp to_timestamp ('2017-04-17 17:43:46 ', 'yyyy-MM-DD HH24: MI: ss ');

? 7. The data returned after the interval is later than 3080, indicating that the modifications after the time point are lost.

Select count (*)

From sphsy. login_table

Where a. id> = 201204171141

? 8. 1 point forward, and 62 deleted items are also returned before the deletion.

Flashback table sphsy. login_table to timestamp to_timestamp ('2017-04-17 17:40:46 ', 'yyyy-MM-DD HH24: MI: ss ');

? 62 rows

Select count (*)

From sphsy. login_table

Where a. id> 201204171078

And a. id <201204171141

? The deleted data is 3074, which indicates that the modification is lost.

Select count (*)

From sphsy. login_table

Where a. id> = 201204171141

/*

Summary: Methods C and D can be restored using data recovery.

Method C is safe and recovery is troublesome. Method d is simple and data may be lost.

*/

[1] [2]