Flashback six major technologies: flashback transaction query and flashback table

Environment:

15:25:40 hr@ORCL (^ω^) select * from v$version where rownum=1;BANNER-------------------------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

I) flashback transaction Query

 

The flashback transaction query is based on the "recovery" of the flashback version query, because in the flashback version query, there is a pseudo column versions_xid, which is the "red line" of these two types of queries ", the flashback_transaction_query view performs a full table scan. Pay attention to the cost evaluation.

14:17:18 sys @ orcl (^ ω ^) DESC flashback_transaction_query name is blank? Type: bytes -------- bytes Xid raw (8) start_scn number start_timestamp date commit_scn number Hour Date logon_user varchar2 (30) undo_change # number operation varchar2 (32) table_name varchar2 (256) table_owner varchar2) row_id varchar2 (9) undo_ SQL varchar2 (4000)

Lab:

Select Operation, undo_ SQL from flashback_transaction_query Q where Q. xid in (select versions_xid from T1 versions between SCN 4198407 and 4198441)/15:34:17 HR @ orcl (^ ω ^) Select region from dual; get_system_change_number ---------------------- 419736415: 34: 22 hr @ orcl (^ ω ^) delete test where rownum = 1; 1 row deleted. 15:34:41 HR @ orcl (^ ω ^) commit; submitted completely. 15:35:08 HR @ orcl (^ ω ^) Select region from dual; get_system_change_number ---------------------- 419740515: 35: 25 hr @ orcl (^ ω ^) Select Xid, commit_scn, operation, undo_sql15: 36: 16 2 from flashback_transaction_query Q where Q. xid in (15:36:16 3 select versions_xid from test versions between SCN 4197364 and 4197405) 15:36:18 4/Xid commit_scn ---------------- ---------- OPERATION----------------------------------------------------------------UNDO_SQL--------------------------------------------------------------------------------06002000F4040000 4197376 deleteinsert into "HR ". "test" ("id1", "ID2") values ('000000', '000000'); 06002000f4040000 2281

**************************************** ************************* **************************************** ***

 

Ii) flashback table

Because flashback table uses DML (Note: you cannot restore the table to the DDL operation that changes the table structure) to restore data, and the rowid cannot be changed, therefore, you must enable the row movement feature before flashback table.

15:57:19 HR @ orcl (^ ω ^) Select row_movement from user_tables where table_name = 't1'; ROW_MOVEMENT----------------DISABLED16: 20: 38 HR @ orcl (^ ω ^) alter table T1 enable row movement; the table has been changed. 16:21:04 HR @ orcl (^ ω ^) Select row_movement from user_tables where table_name = 't1'; ROW_MOVEMENT----------------ENABLED16: 21: 09 HR @ orcl (^ ω ^) Select dbms_flashback.get_system_change_number from dual; get_system_change_number ---------------------- 419991816: 22: 52 HR @ orcl (^ ω ^) Select count (*) from T1; count (*) ---------- 816: 23: 22 hr @ orcl (^ ω ^) Delete T1 where rownum = 1; delete 1 row. 16:23:31 HR @ orcl (^ ω ^) commit; submitted completely. 16:23:36 HR @ orcl (^ ω ^) flashback table T1 to SCN 4199918; flash back complete. 16:24:19 HR @ orcl (^ ω ^) Select count (*) from T1; count (*) -------- 8