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