Oracle10g Flashback Query數據閃回
2024-08-29 13:37:17
供稿：網友
Oracle10g通過Flashback Version Query提供查看對數據庫事務級改變的方法. 當閃回事務處理查詢與閃回版本查詢同時使用時，我們可以輕易地從用戶或者應用程序錯誤恢復。 以下是閃回版本查詢的示例: 1.執行DML操作 EYGLE on 30-MAR-05 >create table t as select username,user_id from dba_users; Table created. EYGLE on 30-MAR-05 >select * from t; USERNAME USER_ID------------------------------ ----------SYSTEM 5SYS 0TEST 25
EYGLE 26SCOTT 29Dip 19TRANS 27TEST1 28OperaTOR 31WMSYS 23DBSNMP 22 USERNAME USER_ID------------------------------ ----------OUTLN 11 12 rows selected.
EYGLE on 30-MAR-05 >delete from t where username='OUTLN'; 1 row deleted. EYGLE on 30-MAR-05 >commit; Commit complete. EYGLE on 30-MAR-05 >delete from t where username='TEST1'; 1 row deleted. EYGLE on 30-MAR-05 >commit; Commit complete. EYGLE on 30-MAR-05 >select * from t; USERNAME USER_ID------------------------------ ----------SYSTEM 5SYS 0TEST 25EYGLE 26SCOTT 29
DIP 19TRANS 27OPERATOR 31WMSYS 23DBSNMP 22 10 rows selected. EYGLE on 30-MAR-05 >update t set user_id=1 where username='EYGLE'; 1 row updated. EYGLE on 30-MAR-05 >commit; Commit complete. EYGLE on 30-MAR-05 >delete from t where user_id >10; 7 rows deleted. EYGLE on 30-MAR-05 >commit; Commit complete. EYGLE on 30-MAR-05 >select * from t;
USERNAME USER_ID------------------------------ ----------SYSTEM 5SYS 0EYGLE 1 EYGLE on 30-MAR-05 >insert into t values('PENNY',2); 1 row created. EYGLE on 30-MAR-05 >commit; Commit complete. 2.執行閃回版本查詢 EYGLE on 30-MAR-05 >select versions_starttime, versions_endtime, versions_xid, 2 versions_operation, username,user_id 3 from t versions between timestamp minvalue and maxvalue 4 / VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V USERNAME USER_ID
------------------------------ ------------------------------ ---------------- - ---------- ----------30-MAR-05 09.34.49 AM 000A000B000000F1 D DBSNMP 2230-MAR-05 09.34.49 AM 000A000B000000F1 D WMSYS 2330-MAR-05 09.34.49 AM 000A000B000000F1 D OPERATOR 3130-MAR-05 09.34.49 AM 000A000B000000F1 D TRANS 2730-MAR-05 09.34.49 AM 000A000B000000F1 D DIP 1930-MAR-05 09.34.49 AM 000A000B000000F1 D SCOTT 2930-MAR-05 09.34.49 AM 000A000B000000F1 D TEST 25
30-MAR-05 09.34.15 AM 0001001900000F0F U EYGLE 130-MAR-05 09.33.51 AM 00080016000000EF D TEST1 2830-MAR-05 09.33.23 AM 0004000A000005EF D OUTLN 11 SYSTEM 5 VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V USERNAME USER_ID------------------------------ ------------------------------ ---------------- - ---------- ---------- SYS 0
30-MAR-05 09.34.49 AM TEST 25 30-MAR-05 09.34.15 AM EYGLE 26 30-MAR-05 09.34.49 AM SCOTT 29 30-MAR-05 09.34.49 AM DIP 19 30-MAR-05 09.34.49 AM TRANS 27 30-MAR-05 09.33.51 AM TEST1 28
30-MAR-05 09.34.49 AM OPERATOR 31 30-MAR-05 09.34.49 AM WMSYS 23 30-MAR-05 09.34.49 AM DBSNMP 22 30-MAR-05 09.33.23 AM OUTLN 11 VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V USERNAME USER_ID------------------------------ ------------------------------ ---------------- - ---------- ----------30-MAR-05 09.49.24 AM 00080006000000EF I PENNY 2
23 rows selected. EYGLE on 30-MAR-05 > 我們可以看到，以上事務的時間以及數據更改。
