oracle 表(table)上最後一次 DML時間、行(row)上最後DML時間

在這介紹兩個oracle 10G開始提供的一個偽列ORA_ROWSCN,它又分為兩種模式一種是基於block,這是預設的模式，還有一種是基於row上，這種模式只能在建裡表時指定ROWDEPENDENCIES，不可以透過後期的alter table ，同時會給資料庫帶來效能負載
每個Block在頭部是記錄了該block最近事務的SCN的，所以預設情況下，只需要從block頭部直接獲取這個值就可以了，不需要其他任何的開銷，Oracle就能做到這一點。但是這明顯第一種模式是scn是不準確的，因為不可能每個事務都能修改整個 塊的資料。

在10g之前，很多系統要實現增量資料抽取，要麼透過解析日誌，要麼加觸發器，要麼就在表上加一個時間截欄位。ORA_ROWSCN其實就是第三種方式，只是這個欄位由Oracle來維護，這樣可以避免一些應用繞過時間截去更新其他欄位帶來的問題。

下面做一個實驗來證明，首先在預設狀態下修改同一個塊上的其中一條資料，然後再啟用行級跟蹤，修改同一塊上的一條資料，觀察ora_rowscn變化

SQL> conn anbob/anbob
Connected.
SQL> create table testscn(
2 id number(5),
3 name varchar2(10),
4 sex number(1),
5 addr varchar2(100)
6 );

Table created.

SQL> insert into testscn values(1,’anbob’,’1′,’beijing’);

1 row created.

SQL> insert into testscn values(2,’sesebook’,’1′,’beijing’);

1 row created.

SQL> insert into testscn values(3,’weejar’,’1′,’beijing’);

1 row created.

SQL> commit;

Commit complete.

SQL> set linesize 150
SQL> select ora_rowscn,id,name,sex,addr from testscn;

ORA_ROWSCN ID NAME SEX
———- ———- ——————– ———-
ADDR
——————————————————————————————————————————————————
46007034 1 anbob 1
beijing

46007034 2 sesebook 1
beijing

46007034 3 weejar 1
beijing

SQL> col addr for a50
SQL> run
1* select ora_rowscn,id,name,sex,addr from testscn

ORA_ROWSCN ID NAME SEX ADDR
———- ———- ——————– ———- ————————————————–
46007034 1 anbob 1 beijing
46007034 2 sesebook 1 beijing
46007034 3 weejar 1 beijing

SQL> select ora_rowscn,id,name,sex,addr,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blockid from testscn;

ORA_ROWSCN ID NAME SEX ADDR BLOCKID
———- ———- ——————– ———- ————————————————– ———-
46007034 1 anbob 1 beijing 1429
46007034 2 sesebook 1 beijing 1429
46007034 3 weejar 1 beijing 1429
——–可以看出記錄是在同一個block上

SQL> select scn_to_timestamp(ora_rowscn) stime,id,name,sex,addr from testscn;

STIME ID NAME SEX ADDR
—————————————- ———- ——————– ———- ————————————————–
16-5月 -11 01.37.54.000000000 下午 1 anbob 1 beijing
16-5月 -11 01.37.54.000000000 下午 2 sesebook 1 beijing
16-5月 -11 01.37.54.000000000 下午 3 weejar 1 beijing

SQL> update testscn set sex=0 where id=2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select scn_to_timestamp(ora_rowscn) stime,id,name,sex,addr from testscn;

STIME ID NAME SEX ADDR
—————————————- ———- ——————– ———- ————————————————–
16-5月 -11 01.47.21.000000000 下午 1 anbob 1 beijing
16-5月 -11 01.47.21.000000000 下午 2 sesebook 0 beijing
16-5月 -11 01.47.21.000000000 下午 3 weejar 1 beijing
–因為是同一個塊上，所以這個塊上的所有資料的scn都更新了

第二種模式
SQL> create table testscn2 ROWDEPENDENCIES as select * from testscn;

Table created.

SQL> select scn_to_timestamp(ora_rowscn) stime,id,name,sex,addr from testscn2;

STIME ID NAME SEX ADDR
—————————————- ———- ——————– ———- ————————————————–
16-5月 -11 02.15.42.000000000 下午 1 anbob 1 beijing
16-5月 -11 02.15.42.000000000 下午 2 sesebook 0 beijing
16-5月 -11 02.15.42.000000000 下午 3 weejar 1 beijing

SQL> select ora_rowscn,id,name,sex,addr,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blockid from testscn;

ORA_ROWSCN ID NAME SEX ADDR BLOCKID
———- ———- ——————– ———- ————————————————– ———-
46007328 1 anbob 1 beijing 1429
46007328 2 sesebook 0 beijing 1429
46007328 3 weejar 1 beijing 1429

SQL> update testscn2 set sex=0 where id=3;

1 row updated.

SQL> commit;

Commit complete.

SQL> select scn_to_timestamp(ora_rowscn) stime,id,name,sex,addr from testscn2;

STIME ID NAME SEX ADDR
—————————————- ———- ——————– ———- ————————————————–
16-5月 -11 02.15.42.000000000 下午 1 anbob 1 beijing
16-5月 -11 02.15.42.000000000 下午 2 sesebook 0 beijing
16-5月 -11 02.16.39.000000000 下午 3 weejar 0 beijing

SQL>

轉載自：

透過dba_tab_modifications/all_tab_modifications/user_tab_modifications檢視獲取表的DML次數和最後DML時間參考如下文章和實驗：

http://blog.itpub.net/9399028/viewspace-712346/

--end--