ORA_ROWSCN

ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is helpful for to know approximately when the rows was last update. It is not absolutely accurate, because Oracle tracks SCNs by transaction committed for the block in which the row resides. It is possible to get more fine-grained approximation of the SCN by creating row-level dependency tracking.

Example:

SQL>create table emp(id number,name char(2000 char));

Table created.

SQL>insert into emp values(1,'Robin');

1 row created.

SQL>insert into emp values(2,'Thomas');

1 row created.

SQL>commit;

Commit complete.

SQL>select id,name,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),ora_rowscn,scn_to_timestamp(ora_rowscn) from emp order by id;

ID

NAME

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

ORA_ROWSCN

SCN_TO_TIMESTAMP

(ORA_ROWSCN)

1

Robin

981439

138849111611

2014-07-24 13:45

2

Thomas

981439

138849111611

2014-07-24 13:45

 






SQL> insert into emp values(3,'Munir');

1 row created.

SQL> insert into emp values(4,'Moon');

1 row created.

SQL> insert into emp values(5,'Suny');

1 row created.

SQL>commit;

Commit complete.

SQL>select id,name,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),ora_rowscn,scn_to_timestamp(ora_rowscn) from emp order by id;

ID

NAME

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

ORA_ROWSCN

SCN_TO_TIMESTAMP(ORA_ROWSCN)

1

Robin

981439

138849111686

2014-07-24 13:47

2

Thomas

981439

138849111686

2014-07-24 13:47

3

Munir

981439

138849111686

2014-07-24 13:47

4

Moon

981435

138849111709

2014-07-24 13:48

5

Suny

981435

138849111709

2014-07-24 13:48

 

From the above table you can see when we inserted new row, id 3 its updated ORA_ROWSCN for all the rows that resides in same block. So it’s not possible to get accurate result when the row was updated, since if any other row insert/update in same block then its change ORA_ROWWSCN number for the every row in same block.

SQL>update emp set name='DON' where id=1;

1 row updated.

SQL>commit;

Commit complete.

SQL>select id,name,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),ora_rowscn,scn_to_timestamp(ora_rowscn) from emp order by id;

ID

NAME

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

ORA_ROWSCN

SCN_TO_TIMESTAMP(ORA_ROWSCN)

1

Don

981439

138849112018

2014-07-24 13:53

2

Thomas

981439

138849112018

2014-07-24 13:53

3

Munir

981439

138849112018

2014-07-24 13:53

4

Moon

981435

138849111709

2014-07-24 13:48

5

Suny

981435

138849111709

2014-07-24 13:48

 

Similar things we can see here, we update row for id 1 and it’s updated all the ORA_ROW_SCN number for other rows as well that resides in the same block.

 

We can get more fine-grained approximation of the SCN by creating table with row-level dependency tracking. There is no additional performance issue we noticed by enabling this features but it’s always better to test it before implanting anything in production environment.

Example:

SQL>create table emp(id number,name char(2000 char)) rowdependencies;

Table created.

SQL>create table emp(id number,name char(2000 char));

Table created.

SQL>insert into emp values(1,'Robin');

1 row created.

SQL>insert into emp values(2,'Thomas');

1 row created.

SQL>select id,name,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),ora_rowscn,scn_to_timestamp(ora_rowscn) from emp order by id;

ID

NAME

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

ORA_ROWSCN

SCN_TO_TIMESTAMP(ORA_ROWSCN)

1

Robin

981447

138849114134

2014-07-24 14:38

2

Thomas

981447

138849114134

2014-07-24 14:38

 

SQL> insert into emp values(3,'Munir');

1 row created.

SQL> insert into emp values(4,'Moon');

1 row created.

SQL> insert into emp values(5,'Suny');

1 row created.

SQL>commit;

 

ID

NAME

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

ORA_ROWSCN

SCN_TO_TIMESTAMP(ORA_ROWSCN)

1

Robin

981447

138849114134

2014-07-24 14:38

2

Thomas

981447

138849114134

2014-07-24 14:38

3

Munir

981447

138849114236

2014-07-24 14:41

4

Moon

981443

138849114236

2014-07-24 14:41

5

Suny

981443

138849114236

2014-07-24 14:41








We can see here new id 3 added in same block but the ORA_ROWSCN number is different after creating the table with rowdependiencies.

SQL>update emp set name='DON' where id=1;

1 row updated.

SQL>commit;

Commit complete.

ID

NAME

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

ORA_ROWSCN

SCN_TO_TIMESTAMP(ORA_ROWSCN)

1

Don

981447

138849114484

2014-07-24 14:46

2

Thomas

981447

138849114134

2014-07-24 14:38

3

Munir

981447

138849114236

2014-07-24 14:41

4

Moon

981443

138849114236

2014-07-24 14:41

5

Suny

981443

138849114236

2014-07-24 14:41

 

Similar things we can see here id 1 is updated and it’s updated the ORA_ROWSCN number only for that rows not the rest of the rows in the same block.

Comments