Oracle Flashback特性(三)



Flashback Transaction Query

一旦我们发现对表做了任何错误或不正确的更改, 可以利用闪回事务查询(Flashback Transaction Query)找出事务包含的任何不适当的更改.

Flashback Transaction Query不象Flashback Version Query需要涉及表有关的DML事务, 只用查询数据字典视图: flashback_transaction_query

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
                 3933733

SQL> update flashtest set object_id=1000
  2  where object_id=15;

1 row updated

SQL> commit;

Commit complete

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
                 3933749

SQL> select versions_startscn startscn, versions_endscn endscn,
  2  versions_xid xid, versions_operation oper,
  3  object_id, object_name
  4  from flashtest
  5  versions between scn 3933733 and 3933749;

STARTSCN  ENDSCN XID                              OPER  OBJECT_ID OBJECT_NAME
————- ———- —————————– ——- ————– ———————–
3933748                 06001D00D90B0000     U          1000        UNDO$
                                                                                    46         I_USER1
                                                                                    28         CON$
              3933748                                                        15         UNDO$
                                                                                    29         C_COBJ#
                                                                                      3         I_OBJ#
                                                                                    25        PROXY_ROLE_DATA$
                                                                                    54         I_CDEF2

8 rows selected

SQL> select start_scn, commit_scn, logon_user,

  2  operation, table_name, undo_sql

  3  from flashback_transcation_query

  4  where xid = hextoraw(’06001D00D90B0000′);

XID               START_SCN START_TIMESTAMP COMMIT_SCN COMMIT_TIMESTAMP LOGON_USER                     UNDO_CHANGE# OPERATION                        TABLE_NAME                                                                       TABLE_OWNER                      ROW_ID              UNDO_SQL
—————- ———- ————— ———- —————- —————————— ———— ——————————– ——————————————————————————– ——————————– ——————- ——————————————————————————–
06001D00D90B0000    3933746 2008-07-07 19:2    3933748 2008-07-07 19:28 WIND                                      1 UPDATE                           FLASHTEST                                                                        WIND                             AAAR41AAEAAABcMAAJ  update “WIND”.”FLASHTEST” set “OBJECT_ID” = ’15′ where ROWID = ‘AAAR41AAEAAABcMA
06001D00D90B0000    3933746 2008-07-07 19:2    3933748 2008-07-07 19:28 WIND                                      2 BEGIN

flashback_transaction_query视图列说明:
XID: 事务ID号
START_SCN: 事务中首个DML操作的SCN
START_TIMESTAMP: 事务中首个DML操作的时间戳
COMMIT_SCN: 事务提交时的SCN
COMMIT_TIMESTAMP: 事务提交时的时间戳
LOGON_USER: 事务的拥有者
UNDO_CHANGE#:
OPERATION: DML操作: DELETE, INSERT, UPDATE, BEGIN或UNKNOWN
TABLE_TABLE: 更改的表
TABLE_OWNER: 更改表的拥有者
ROW_ID: 更改行的ROWID
UNDO_SQL: 撤消DML操作的SQL语句