在做實驗，遇到這個函式不知道啥意思，就記錄一下。

ORA_ROWSCN Pseudocolumn

For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking. Please refer to CREATE TABLE ...  for more information on row-level dependency tracking.

You cannot use this pseudocolumn in a query to a view. However, you can use it to refer to the underlying table when creating a view. You can also use this pseudocolumn in the WHERE clause of an UPDATE or DELETE statement.

ORA_ROWSCN is not supported for Flashback Query. Instead, use the version query pseudocolumns, which are provided explicitly for Flashback Query. Please refer to the SELECT ...  for information on Flashback Query and  for additional information on those pseudocolumns.

Restriction: This pseudocolumn is not supported for external tables.

Example The first statement below uses the ORA_ROWSCN pseudocolumn to get the system change number of the last operation on the employees table. The second statement uses the pseudocolumn with the SCN_TO_TIMESTAMP function to determine the timestamp of the operation:

SELECT ORA_ROWSCN, last_name FROM employees WHERE employee_id = 188; SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN), last_name FROM employees WHERE employee_id = 188

一下是轉帖（懶得自己寫了）

今天看到一個新函式ora_rowscn(其實這個函式也不新了，從oracle10g R1 就有了，只是我不知道，嘿嘿。。。)，順道在這裡記錄下。

 ORA_ROWSCN是建立在oracle系統時鐘（SCN）基礎上，在表級應用的函式。

下例所示，在查詢語句裡直接呼叫ora_rowscn，就就能顯示出每行最後一次的SCN，

Example1:

bu5705@ADW1U>select a.*,dbms_rowid.rowid_block_number(rowid),to_char(ora_rowscn) from test1 a;

 

       SID    SERIAL# DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) TO_CHAR(ORA_ROWSCN)

---------- ---------- ------------------------------------ ----------------------------------------

      4574       5225                                20552 10763660876119

      4582       4371                                20552 10763660876119

      4583      30781                                20552 10763660876119

      4589      15658                                20552 10763660876119

      4551       2301                                20554 10763660894774

      4555       4724                                20554 10763660894774

      4563       7463                                20554 10763660894774

      4567       7283                                20554 10763660894774

在上面的例子中，我分兩批插入的資料，我們可以看到這兩批資料的 scn好是不一樣的。

在這裡要特別注意的在預設狀態下，oracle是在塊級維護scn的。   

Example2:

bu5705@ADW1U>update test1 set serial#=12345 where sid=4574; 

1 row updated. 

bu5705@ADW1U>commit;

Commit complete.

bu5705@ADW1U>select a.*,dbms_rowid.rowid_block_number(rowid),to_char(ora_rowscn) from test1 a;

 

       SID    SERIAL# DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) TO_CHAR(ORA_ROWSCN)

---------- ---------- ------------------------------------ ----------------------------------------

      4574      12345                                20552 10763669433340

      4582       4371                                20552 10763669433340

      4583      30781                                20552 10763669433340

      4589      15658                                20552 10763669433340

      4551       2301                                20554 10763660894774

      4555       4724                                20554 10763660894774

      4563       7463                                20554 10763660894774

      4567       7283                                20554 10763660894774        

在Example2裡我們可以清楚的看到我們只修改了sid=4574那一行的值，但是所有在block#為20552上所有資料的scn都因此而改變了。     

假若想從行級跟蹤SCN，就必須在create table   是定義rowdependencies.另外要注意的是這種改變

不能用簡單的alter table語句來實現，必須重建table。

Example3:

bu5705@ADW1U>drop table test1;

Table dropped.

bu5705@ADW1U>create table test1 rowdependencies as select sid,serial# from v$session where rownum<5;

Table created.

bu5705@ADW1U>select a.*,dbms_rowid.rowid_block_number(rowid),to_char(ora_rowscn) from test1 a;

       SID    SERIAL# DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) TO_CHAR(ORA_ROWSCN)

---------- ---------- ------------------------------------ ----------------------------------------

      4534       5955                                20552 10763669640521

      4540       1724                                20552 10763669640521

      4550      28663                                20552 10763669640521

      4561      28915                                20552 10763669640521

 

bu5705@ADW1U>update test1 set serial#=12345 where sid=4534;

1 row updated. 

bu5705@ADW1U>commit;

Commit complete. 

bu5705@ADW1U>select a.*,dbms_rowid.rowid_block_number(rowid),to_char(ora_rowscn) from test1 a;

       SID    SERIAL# DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) TO_CHAR(ORA_ROWSCN)

---------- ---------- ------------------------------------ ----------------------------------------

      4534      12345                                20552 10763669664049

      4540       1724                                20552 10763669640521

      4550      28663                                20552 10763669640521

      4561      28915                                20552 10763669640521

      最後還有一個小技巧，我們還可以透過使用scn_to_timestamp() 函式來轉化scn，從而得知修改的時間

bu5705@ADW1U>select a.*, scn_to_timestamp(ora_rowscn) from test1 a;

       SID    SERIAL# SCN_TO_TIMESTAMP(ORA_ROWSCN)

---------- ---------- ---------------------------------------------------------------------------

      4574       5225 24-APR-12 03:58:33.000000000

      4582       4371 24-APR-12 03:58:33.000000000

      4583      30781 24-APR-12 03:58:33.000000000

      4589      15658 24-APR-12 03:58:33.000000000

      4551       2301 24-APR-12 03:59:27.000000000

      4555       4724 24-APR-12 03:59:27.000000000

      4563       7463 24-APR-12 03:59:27.000000000

      4567       7283 24-APR-12 03:59:27.000000000