1.What are the prerequisites for performing flashback transactions? (Choose all that apply)
A.Supplemental log must be enabled
B.Supplemental log must be enabled for the primary key
C.Undo retention guarantee for the database must be configured
D.”EXECUTE “ permission on the DBMS_FLASHBACK package must be granted to the user
答案：ABD
解析：這裡問的是閃回事務的先覺條件
SQL> alter database add SUPPLEMENTAL log data; --打開附加日志
Database altered.
SQL> alter database add SUPPLEMENTAL log data(primary key) columns; --打開帶主鍵的附加日志
Database altered.
SQL> select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui from v$database; --檢視狀态
SUPPLEME SUP SUP
-------- --- ---
YES YES NO
SQL> select versions_xid,versions_startscn,versions_operation,id,name from test versions between scn minvalue and maxvalue where id=1 --檢視具體的xid和操作方式
VERSIONS_XID VERSIONS_STARTSCN V ID NAME
---------------- ----------------- - ---------- ----------------------------------------------
02001100A1120000 8338066 U 1 asdf
1 ll
SQL> /
SQL> select undo_sql from flashback_transaction_query where xid='02001100A1120000'; --可以查到具體的撤回sql語句了
UNDO_SQL
--------------------------------------------------------------------------------
update "BK"."TEST" set "NAME" = 'll' where ROWID = 'AAAjvnAAHAAAACXAAA';
SQL> alter database drop SUPPLEMENTAL log data(primary key) columns; --關閉undo語句日志，這裡注意需要反向關閉
SQL> alter database drop SUPPLEMENTAL log data;
--這裡已經查出來了，這個DBMS_FLASHBACK描述如下
Using DBMS_FLASHBACK, you can flash back to a version of the database at a specified wall-clock time or a specified system change number (SCN).
execute dbms_flashback.enable_at_time(sysdate-40/1440); --恢複之前的記錄(這裡需要注意隻是針對該會話，恢複後進行對資料處理,如果退出重新登陸那麼将恢複)
execute dbms_flashback.disable();--在執行完成後，必須進行disable，否則不能在執行enable_at_tim
select dbms_flashback.get_system_change_number() from dual; 8301691 --查詢一下目前的scn号碼
execute dbms_flashback.enable_at_system_change_number(8301691); --閃回到那個scn上，新增加的行沒有了
這裡dbms_flashback還有其他方法，可以查文檔，這裡感覺AB也可以，D不是必須的