|
Flashback II
|
This week we want to continue the experiment of Flashback. This time the topic is the third one of query features, transaction log flashback. Transaction log flashback does not query the old data or the change time but to refer to the method to recover the data back to its original status. The data will not be recovered to its original status, but only the method to recover will be referred to. Transaction log flashback refers to FLASHBACK_TRANSACTION_QUERY dictionary view, so it is necessary to execute it as a user with DBA authority. We actually mentioned this dictionary view last time, but letfs review it again.
SQL> DESC FLASHBACK_TRANSACTION_QUERY
Name Null? Type
----------------------------------------- -------- ----------------------
XID RAW(8)
START_SCN NUMBER
START_TIMESTAMP DATE
COMMIT_SCN NUMBER
COMMIT_TIMESTAMP DATE
LOGON_USER VARCHAR2(30)
UNDO_CHANGE# NUMBER
OPERATION VARCHAR2(32)
TABLE_NAME VARCHAR2(256)
TABLE_OWNER VARCHAR2(32)
ROW_ID VARCHAR2(19)
UNDO_SQL VARCHAR2(4000)
|
XID : Transaction ID
START_SCN : SCN when the transaction started
START_TIMESTAMP : Time when the transaction started
COMMIT_SCN : SCN when the transaction was committed
COMMIT_TIMESTAMP : Time when the transaction was committed
LOGON_USER : User who executed the transaction
UNDO_CHANGE# : UNDO system change number (higher than 1)
OPERATION : operations executed
TABLE_NAME : name of the table of the operation
TABLE_OWNER : owner of the table
ROW_ID : ROW ID of the row of the operation
UNDO_SQL : SQL statement to undo the operation
|
Let's connect as SYSDBA and specify XID to refer to UNDO_SQL row of FLASHBACK_TRANSACTION_QUERY. The XID is specified as the case at the UPDATE SQL statement, marked as (4) in the experiment explanation two weeks ago. .
SQL> CONN / AS SYSDBA
SQL> SELECT TABLE_NAME ,OPERATION,UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID='04002B00CD2D0000';@ (to specify XID)
TABLE_NAME OPERATION UNDO_SQL
---------- -------------------------------- ---------------------------------
FLASHBACK_TEST UPDATE update "ORAORA"."FLASHBACK_TEST"
set "COL2" = 'AAA' where ROWID =
'AAANkuAAFAAAAHYAAA';
|
As long as XID is specified, the SQL to redo the transaction can be obtained. Therefore, to undo all transactions, you can just execute all the UNDO_SQL statements. The process is to execute UNDO_SQL while confirming the change history of data by using row log flashback. We will introduce the experiment procedures next time.
Takahisa Omichi
|
|
