oracle之flashback深入研究(四)
s of scn 11022133;
ID SAL JOB
---------- ---------- ----------
1 2 a
2 3 b
3 4 c
1 3 b
2 1 a
D
6 rows selected.
SQL>
2）闪回版本查询：
SQL> r
1* select * from t1
ID SAL JOB
---------- ---------- ----------
1 9 ab
1 2 ab
2 3 b
1 3 ab
2 1 a
D
6 rows selected.
SQL> delete from t1 where id=1;
3 rows deleted.
SQL> commit;
Commit complete.
SQL> select versions_startscn,versions_endscn,versions_xid,versions_operation,id,sal,job from t1 versions between scn minvalue and maxvalue;
VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V ID SAL JOB
----------------- --------------- ---------------- - ---------- ---------- ----------
11144497 0D001600D8000000 D 1 9 z
11144497 1 9 z
11144497 0D001600D8000000 D 1 3 b
11144497 0D001600D8000000 D 1 2 a
11054404 11000300BF000000 D 3 4 c
11144497 1 2 a
2 3 b
11054404 3 4 c
11144497 1 3 b
2 1 a
D
11 rows selected.
SQL>
SQL>
然后我们更具versions_xid找到相应的事务
3）事务查询；
借助flashback_transaction_query这个视图；
首先看一下 这个视图结构；
SSQL> 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)
SQL>
然后我们进行一次查询：
eg：
SQL> conn rhys/root
Connected.
SQL> select * from t1;
ID SAL JOB
---------- ---------- ----------
2 3 b
3 1 a
D
1 1 ab
SQL> delete from t1 where id=1;
1 row deleted.