ORA_ROWSCN, NOROWDEPENDENCIES, ROWDEPENDENCIES in Oracle

This article shows how works table pseudocolumn ORA_ROWSCN. The column provides SCN (System Change Number) when last DML was executed on block or row level for a table . SCN system change number can be easily translated as timestamp using function SCN_TO_TIMESTAMP. The SCN as timestamp is approximate to 3 seconds.

When new table is created Oracle adds as default NOROWDEPENDENCIES clause.

CREATE TABLE ... NOROWDEPENDENCIES

You can as well add explicite ROWDEPENDENCIES caluse

CREATE TABLE ... ROWDEPENDENCIES

For both statements Oracle adds extra pseudocolumn to the table ORA_ROWSCN. The column provides SCN (System Change Number) when last DML was executed on block or row level. SCN system change number can be easily translated as timestamp using function SCN_TO_TIMESTAMP.

Clause ORA_ROWSCN
provides SCN on level
NOROWDEPENDENCIES block
ROWDEPENDENCIES row

Let’s create sample table TEST_TBL. Oracle creates the table with NOROWDEPENDENCIES clause.

DROP TABLE test_tbl;

CREATE TABLE test_tbl
(
  id1 NUMBER,
  id2 DATE
);

SELECT table_name, dependencies
  FROM user_tables
 WHERE table_name='TEST_TBL';

TABLE_NAME   DEPENDENCIES
------------ -----------------
TEST_TBL     DISABLED

DROP TABLE test_tbl;

CREATE TABLE test_tbl
(
  id1 NUMBER,
  id2 DATE
) NOROWDEPENDENCIES;

SELECT table_name, dependencies
  FROM user_tables
 WHERE table_name='TEST_TBL';

TABLE_NAME   DEPENDENCIES
------------ -----------------
TEST_TBL     DISABLED

and function get_rtowid_info to get block information where a rowid is stored in a table.

CREATE OR REPLACE FUNCTION get_rowid_info
(
  rowid_data ROWID,
  rowid_info VARCHAR2,
  file_type  VARCHAR2
)
RETURN VARCHAR2
IS
  ridtyp NUMBER;
  objnum NUMBER;
  relfno NUMBER;
  blno   NUMBER;
  rowno  NUMBER;
BEGIN
  dbms_rowid.rowid_info(rowid_data,ridtyp,objnum,
                        relfno,blno,rowno,file_type);
  
  IF rowid_info = 'TYPE' THEN
    RETURN to_char(ridtyp);
  ELSIF rowid_info = 'OBJECT' THEN
    RETURN to_char(objnum);
  ELSIF rowid_info = 'FILE' THEN
    RETURN to_char(relfno);
  ELSIF rowid_info = 'BLOCK' THEN
    RETURN to_char(blno);
  ELSE
    RETURN to_char(rowno);
  END IF;  
END;
/

table TEST_TBL was created on smallfile type datafiles so SMALLFILE is used as parameter later for function GET_ROWID_INFO

SELECT t.tablespace_name, t.bigfile
  FROM user_tablespaces t, user_users u
WHERE t.tablespace_name = u.default_tablespace;

TABLESPACE_NAME   BIGFILE
----------------- -------
USERS             NO

Let’s insert dummy data into the table

BEGIN
  FOR i IN 1..2
  LOOP
   INSERT INTO test_tbl VALUES(i, sysdate);
  END LOOP;
  
  COMMIT;
END;
/

read information about rowid. You can notice all data are in the same block and ORA_ROWSCN is the same

SELECT 
  ORA_ROWSCN, 
  get_rowid_info(ROWID, 'BLOCK', 'SMALLFILE') BLOCK, 
  to_char(SCN_TO_TIMESTAMP(ORA_ROWSCN), 'HH24:MI:SS') TIMESTAMP, 
  id1, to_char(id2, 'HH24:MI:SS')
FROM test_tbl;

ORA_ROWSCN  BLOCK TIMESTAMP  id1 id2
----------- ----- ---------  --- ---------
    3266015   260  22:08:59    1  22:09:01
    3266015   260  22:08:59    2  22:09:01

Inserting new 2 rows later will update ORA_ROWSCN for the same block

BEGIN
  dbms_lock.sleep(10);
  
  FOR i IN 1..2
  LOOP
   INSERT INTO test_tbl VALUES(i, sysdate);
  END LOOP;
  
  COMMIT;
END;
/

SELECT 
  ORA_ROWSCN, 
  get_rowid_info(ROWID, 'BLOCK', 'SMALLFILE') BLOCK, 
  to_char(SCN_TO_TIMESTAMP(ORA_ROWSCN), 'HH24:MI:SS') TIMESTAMP, 
  id1, to_char(id2, 'HH24:MI:SS') id2
FROM test_tbl;

ORA_ROWSCN  BLOCK TIMESTAMP  id1 id2
----------- ----- ---------  --- ---------
3266059    260    22:10:29    1    22:09:01
3266059    260    22:10:29    2    22:09:01
3266059    260    22:10:29    1    22:10:32
3266059    260    22:10:29    2    22:10:32

if new data is inserted in different block it will get different ORA_ROWSCN

INSERT /*+ APPEND */ INTO test_tbl
SELECT * FROM test_tbl
WHERE ROWNUM < 3;

COMMIT;

SELECT 
  ORA_ROWSCN, 
  get_rowid_info(ROWID, 'BLOCK', 'SMALLFILE') BLOCK, 
  to_char(SCN_TO_TIMESTAMP(ORA_ROWSCN), 'HH24:MI:SS') TIMESTAMP, 
  id1, to_char(id2, 'HH24:MI:SS') id2
FROM test_tbl;

ORA_ROWSCN  BLOCK TIMESTAMP  id1 id2
----------- ----- ---------  --- ---------
3266059    260    22:10:29    1    22:09:01
3266059    260    22:10:29    2    22:09:01
3266059    260    22:10:29    1    22:10:32
3266059    260    22:10:29    2    22:10:32
3266168    264    22:14:20    1    22:12:49
3266168    264    22:14:20    2    22:12:49

Another example simple update will modify ORA_ROWSCN on data block level

UPDATE test_tbl
  SET id1=3, id2=sysdate
WHERE ROWNUM=1;

COMMIT;

SELECT 
  ORA_ROWSCN, 
  get_rowid_info(ROWID, 'BLOCK', 'SMALLFILE') BLOCK, 
  to_char(SCN_TO_TIMESTAMP(ORA_ROWSCN), 'HH24:MI:SS') TIMESTAMP, 
  id1, to_char(id2, 'HH24:MI:SS') id2
FROM test_tbl;

ORA_ROWSCN  BLOCK TIMESTAMP  id1 id2
----------- ----- ---------  --- ---------
3266486    260    22:21:44    3    22:21:43
3266486    260    22:21:44    2    22:12:49
3266486    260    22:21:44    1    22:13:04
3266486    260    22:21:44    2    22:13:04
3266168    264    22:14:20    1    22:12:49
3266168    264    22:14:20    2    22:12:49

Block level stamp using ORA_ROWSCN is nice option but is not precise. It can’t be used to get information when last time a row was modified. It can be done by adding ROWDEPENDENCIES clause during table creation. It will create ORA_ROWSCN column with granularity row.

DROP TABLE test_tbl;

CREATE TABLE test_tbl
(
  id1 NUMBER,
  id2 DATE
) ROWDEPENDENCIES;

Following example inserts 9 records to the same data block. Each record is inserted as separate transaction but this time each record in the same block has different SCN number.

ORA_ROWSCN is translated with function SCN_TO_TIMESTAMP as timestamp with precision 3 seconds. You can see 3 groups of data so it can’t be used as very precise method to check when last time the record was modified.

BEGIN
  FOR i IN 1..9
  LOOP
   INSERT INTO test_tbl VALUES(i, sysdate);
   dbms_lock.sleep(1);
   COMMIT;
  END LOOP;
END;
/

SELECT 
  ORA_ROWSCN, 
  get_rowid_info(ROWID, 'BLOCK', 'SMALLFILE') BLOCK, 
  to_char(SCN_TO_TIMESTAMP(ORA_ROWSCN), 'HH24:MI:SS') TIMESTAMP, 
  id1, to_char(id2, 'HH24:MI:SS') id2
FROM test_tbl;

ORA_ROWSCN  BLOCK TIMESTAMP  id1 id2
----------- ----- ---------  --- ---------
3268743    276    22:43:24    1    22:43:24
3268745    276    22:43:24    2    22:43:25
3268747    276    22:43:24    3    22:43:26
3268749    276    22:43:27    4    22:43:27
3268751    276    22:43:27    5    22:43:28
3268753    276    22:43:27    6    22:43:29
3268755    276    22:43:30    7    22:43:30
3268757    276    22:43:30    8    22:43:31
3268759    276    22:43:30    9    22:43:32

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *