ORA_ROWSCN
Flashback Query
??? Timestamp or SCN
??? Version

SQL> create table t as select rownum as x,rownum as y from dual connect by rownum<=3;

通過rowid解析block number和row number，看出這三條數據的block number相同，說明都在同一個data block里面
SQL> select dbms_rowid.rowid_block_number(rowid) blk, dbms_rowid.rowid_row_number(rowid) rno, ora_rowscn,x,y from t;

?????? BLK??????? RNO ORA_ROWSCN????????? X????????? Y
---------- ---------- ---------- ---------- ----------
???? 89512????????? 0? 562061685????????? 1????????? 1
???? 89512????????? 1? 562061685????????? 2????????? 2
???? 89512????????? 2? 562061685????????? 3????????? 3

ORA_ROWSCN

默認情況下ORA_ROWSCN紀錄的是一個data block最后被修改的scn，所以即使只修改一行數據，整個block里面的每行都會反映出來。這個偽列在處理樂觀鎖定的時候很有用處。

SQL> update t set y=1 where x=1;

僅僅update而不做commit的時候，rowscn不會變化，仍然是562061685
SQL> select ora_rowscn,x,y from t;
ORA_ROWSCN????????? X????????? Y
---------- ---------- ----------
?562061685????????? 1????????? 1
?562061685????????? 2????????? 2
?562061685????????? 3????????? 3

SQL> commit;
Commit complete.

commit之后再次查詢，可以看到整個block都發生了變化
SQL> select ora_rowscn,x,y from t;

ORA_ROWSCN????????? X????????? Y
---------- ---------- ----------
?562327939????????? 1????????? 1
?562327939????????? 2????????? 2
?562327939????????? 3????????? 3

如果在建表的時候使用 rowdependencies 屬性，ORA_ROW_SCN就會記錄每行最后修改的scn。使用這個屬性，每行會增加6byte的存儲空間。

SQL> create table t2 rowdependencies as select rownum as x,rownum as y from dual connect by rownum<=3;
Table created.

SQL> select dbms_rowid.rowid_block_number(rowid) blk, dbms_rowid.rowid_row_number(rowid) rno, ora_rowscn,x,y from t2;
?????? BLK??????? RNO ORA_ROWSCN????????? X????????? Y
---------- ---------- ---------- ---------- ----------
??? 176216????????? 0? 569148721????????? 1????????? 1
??? 176216????????? 1? 569148721????????? 2????????? 2
??? 176216????????? 2? 569148721????????? 3????????? 3

SQL> update t2 set y=1 where x=1;
1 row updated.
SQL> commit;
Commit complete.

雖然三行屬于同一個block，但是沒行的ora_rowscn卻不同。
SQL> select dbms_rowid.rowid_block_number(rowid) blk, dbms_rowid.rowid_row_number(rowid) rno, ora_rowscn,x,y from t2;
?????? BLK??????? RNO ORA_ROWSCN????????? X????????? Y
---------- ---------- ---------- ---------- ----------
??? 176216????????? 0? 569149392????????? 1????????? 1
??? 176216????????? 1? 569148721????????? 2????????? 2
??? 176216????????? 2? 569148721????????? 3????????? 3

Flashback Query by Timestamp or SCN

在比較短的時間里，多次對一條數據進行修改，并且紀錄下每次修改前的date和scn

SQL> update t set y=0 where x=1;
SQL> commit;

SQL> select to_char(systimestamp,'yyyymmdd hh24:mi:ss.SSSSS') ts,dbms_flashback.get_system_change_number as scn from dual;
TS???????????????????????????? SCN
----------------------- ----------
20080520 20:15:44.72944? 562860704
SQL> update t set y=y+1 where x=1;
SQL> commit;
Commit complete.

SQL> select to_char(systimestamp,'yyyymmdd hh24:mi:ss.SSSSS') ts,dbms_flashback.get_system_change_number as scn from dual;
TS???????????????????????????? SCN
----------------------- ----------
20080520 20:16:15.72975? 562869199
SQL> update t set y=y+1 where x=1;
SQL> commit;
Commit complete.

SQL> select to_char(systimestamp,'yyyymmdd hh24:mi:ss.SSSSS') ts,dbms_flashback.get_system_change_number as scn from dual;
TS???????????????????????????? SCN
----------------------- ----------
20080520 20:16:24.72984? 562872846
SQL> update t set y=y+1 where x=1;
SQL> commit;
Commit complete.

有了這些date和scn之后，就可以使用flashback query來查詢某個時間點的數據
SQL> select ora_rowscn,x,y from t as of timestamp to_timestamp('20080520 20:15:40','yyyymmdd hh24:mi:ss');
ORA_ROWSCN????????? X????????? Y
---------- ---------- ----------
?562857027????????? 1????????? 0
?562857027????????? 2????????? 2
?562857027????????? 3????????? 3

ORACLE把這個指定的timestamp轉換成scn進行查詢。我們也可以使用函數 timestamp_to_scn() 和 scn_to_timestamp() 來進行轉換。

sys.smon_scn_time 這個表紀錄了這個對應關系。官方文檔說明，這個表每5分鐘更新一次，如果找不到精確的匹配，就采用四舍五入找到最接近的scn。（但是使用上面紀錄的時間，都找到了精確的匹配，所以對這個5分鐘的理解還不太明白？不過可以肯定的是，使用scn一定可以找到精確的值）
SQL> select * from (select to_char(time_dp,'yyyymmdd hh24:mi:ss') time_dp,scn from sys.smon_scn_time order by 1 desc) where rownum<=5;

TIME_DP????????????????? SCN
----------------- ----------
20080520 20:25:56? 563045354
20080520 20:20:53? 562958208
20080520 20:15:09? 562852192
20080520 20:09:58? 562754524
20080520 20:03:59? 562639175

TIMESTAMP_TO_SCN
另外做一個測試，在更短的時間內修改數據，證明使用timestamp的確不能精確反映scn，但是沒有仍然找到這個時間間隔是多少。
create table t1(x int, y int, t timestamp(9));
insert into t1(x,y,t) values(1,0,systimestamp);
commit;

-- 這個代碼能夠返回每次update時刻的timestamp，和轉換得到的scn
declare
? l_t timestamp(9);
begin
? update t1 set y = 0;
? commit;
? for i in 1 .. 5 loop
??? update t1 set y = y + 1, t = systimestamp returning t into l_t;
??? commit;
??? dbms_output.put_line(to_char(l_t)||' ---- '||timestamp_to_scn(l_t));
??? dbms_lock.sleep(2);
? end loop;
end;
/

運行結果如下。代碼中進行了5次commit，每次間隔1秒。我們知道至少應該有5個不同的scn，但是通過轉換僅僅得到2個不同的scn
20-MAY-08 09.47.03.738067 PM ---- 564380246
20-MAY-08 09.47.04.740171 PM ---- 564380246
20-MAY-08 09.47.05.742007 PM ---- 564380246
20-MAY-08 09.47.06.743822 PM ---- 564381615
20-MAY-08 09.47.07.745620 PM ---- 564381615

間隔時間2秒，得到了3個不同的scn
20-MAY-08 09.53.53.547296 PM ---- 564516228
20-MAY-08 09.53.55.548386 PM ---- 564517232
20-MAY-08 09.53.57.550024 PM ---- 564517232
20-MAY-08 09.53.59.551694 PM ---- 564518469
20-MAY-08 09.54.01.553324 PM ---- 564518469

間隔時間5秒，得到了5個不同的scn
20-MAY-08 09.49.06.384516 PM ---- 564421038
20-MAY-08 09.49.11.385069 PM ---- 564421874
20-MAY-08 09.49.16.386223 PM ---- 564423813
20-MAY-08 09.49.21.387317 PM ---- 564425644
20-MAY-08 09.49.26.388466 PM ---- 564427012

flashback version query

如果在更新數據的時候沒有記下time或者scn，又想知道某行數據或表的更新歷史，就需要使用flashback version query功能
select versions_starttime, versions_endtime, versions_operation, x, y
? from t versions between timestamp minvalue and maxvalue
?where versions_starttime >
?????? to_timestamp('20080520 20:15:40', 'yyyymmdd hh24:mi:ss');

另外一個偽列 xid 表示transaction id，可以用來查詢視圖 flashback_transaction_query 或者 v$logmnr_contents
SQL> /
VERSIONS_STARTTIME???????????? VERSIONS_ENDTIME?????????????? V????????? X????????? Y
------------------------------ ------------------------------ - ---------- ----------
20-MAY-08 08.16.27 PM???????????????????????????????????????? U????????? 1????????? 3
20-MAY-08 08.16.18 PM????????? 20-MAY-08 08.16.27 PM????????? U????????? 1????????? 2
20-MAY-08 08.15.53 PM????????? 20-MAY-08 08.16.18 PM????????? U????????? 1????????? 1

轉載于:https://www.cnblogs.com/wait4friend/archive/2012/01/11/2334569.html

總結

以上是生活随笔為你收集整理的Flashback Query笔记的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。