Flashback six major technologies: flashback transaction query and flashback table

Source: Internet
Author: User

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

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.