Update time for Oracle ORA_ROWSCN Row Records

In this introduction two Oracle 10G began to provide a pseudo-column ORA_ROWSCN, it is divided into two modes one is based on block, which is the default mode, there is a row, this mode can only be built in the table when the rowdependencies is specified, It is not possible to pass the late ALTER TABLE, while bringing a performance load to the database
Each block in the head records the SCN of the block's recent transaction, so by default it is only necessary to get this value directly from the block header, which Oracle can do without any additional overhead. But this obvious first pattern is that the SCN is inaccurate, because it is not possible for each transaction to modify the entire block of data.

Before 10g, many systems implemented incremental data extraction, either by parsing the log, by adding a trigger, or by adding a time-truncated field to the table. ORA_ROWSCN is actually the third way, but this field is maintained by Oracle, which avoids the problem of some applications bypassing the time to update other fields.

Here is an experiment to prove that first in the default state to modify one of the data on the same block, and then enable row-level tracking, modify the same piece of data, observe ORA_ROWSCN changes

Sql> conn anbob/anbobconnected.sql> CREATE TABLE TESTSCN (2 ID number (5), 3 name VARCHAR2 (ten), 4 sex number (1), 5 addr V ARCHAR2 (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 150sql> select ora_rowscn,id,name,sex,addr from TESTSCN;ORA_ROWSCN ID name sex ———-———-—————— –-- --addr —————————————————————————————————————————————————— 46007034 1 anbob 1beijing46007034 2 Sesebook 1beijing46007034 3 Weejar 1beijingsql> col addr for a50sql> run1* select ora_rowscn,id,name,sex,addr from TestscnORA _ROWSCN ID NAME SEX ADDR ———-———-—————— – ———-———————————————— –46007034 1 anbob 1 beijing46007034 2 Sesebook 1 beijing460 07034 3 Weejar 1 beijingsql> 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 142946007034 2 Sesebook 1 Beijing 142946007034 3 weejar 1 Beijing 1429--– You can see that the record is on the same block sql> select Scn_to_timestamp (ORA_ROWSCN) St Ime,id,name,sex,addr from TESTSCN; Stime ID NAME SEX ADDR —————————————-———-—————— – ———-———————————————— – 1 June-May – 11 01.37.54.000000000 pm 1 anbob 1 beijing1 June-May-11 01.37.54.000000000 pm 2 sesebook 1 beijing16-5 Month-11 01.37.54.000000000 PM 3 weejar 1 beijingsql> update TESTSC N 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 —————————————-———-—————— – ———-———————————————— – 1 June-May – 11 01.47.21.000000000 pm 1 anbob 1 beijing1 June-May-11 01.47.21.000000000 pm 2 Sesebook 0 beijing16-5 Month-11 01.47.21.000000000 PM 3 Weejar 1 beijing– because it's the same block, so all the data on this block The SCN has updated the second mode 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 —————————————-———-—————— – ———-———————————————— – 1 June-May – 11 02.15.42.000000000 pm 1 anbob 1 beijing1 June-May-11 02.15.42.000000000 pm 2 Sesebook 0 beijing16-5 Month-11 02.15.42.000000000 PM 3 weejar 1 beijingsql> Select Ora_ro Wscn,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 142946007328 2 Sesebook 0 Beijing 142946007328 3 weejar 1 Beijing 1429sql> Update tests CN2 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 —————————————-———-—————— – ———-———————————————— – 1 June-May – 11 02.15.42.000000000 pm 1 anbob 1 beijing1  June-May-11 02.15.42.000000000 pm 2 Sesebook 0 beijing16-5 Month-11 02.16.39.000000000 PM 3 Weejar 0 beijingsql>

Top

Update time for Oracle ORA_ROWSCN Row Records