DB GEEK

Hi DBgeek Army,
Welcome back to the world of Oracle!
Today, we will talk about Pseudocolumn.
Oh, wait. Have you read my previous notes on Pseudocolumn?
Click Here to read my notes on very important Pseudocolumns ROWID and ROWNUM.

ORA_ROWSCN is also a Pseudocolumn which returns the conservative upper bound system change number of the most recent change to the row. It helps users to know the date and time when any INSERT/UPDATE statements was issued to the Table. In simple words, we can say that this column gets populated/updated when a row gets imported/updated. Unfortunately, It is not absolutely precise because Oracle tracks SCNs by transaction committed for the block in which the row resides. It means, if a block contains 50 rows and only 5 rows updated via UPDATE statement, ORA_ROWSCN will get updated for all the rows (all 50 rows) available in that block. So user cannot identify ORA_ROWSCN which particular rows were get updated.
ORA_ROWSCN returns a numeric value and we can convert this value into the TIMESTAMP using the SCN_TO_TIMESTAMP built in function.

Let’s create a table insert 2000 rows. I am using PLSQL block with FOR LOOP to insert 2000 rows.

--create table
CREATE TABLE test_scn (
	col1 VARCHAR2(50)
);
/
BEGIN
	FOR i IN 1..2000 LOOP
		INSERT INTO test_scn VALUES ( 'test' || ( i ) );
	END LOOP;
	COMMIT;
END;
/

So, I have created a table named test_scn and inserted 2000 rows into it. Let’s check the table using the GROUP BY clause on the ORA_ROWSCN Pseudocolumn.

SELECT
	scn_to_timestamp(ora_rowscn),
	COUNT(col1)
FROM
	test_scn
GROUP BY
	ora_rowscn;

You can see in the above result output that 2000 rows were inserted into the table at a specific TIMESTAMP. Every row has the same TIMESTAMP because when we committed the transaction inside the PLSQL block it took almost NO TIME to store it on the disk.

Let’s update only 1 row in the table and see the same results.

UPDATE test_scn
SET
	col1 = 'test_test'
WHERE
	col1 = 'test4';

COMMIT;

SELECT
	scn_to_timestamp(ora_rowscn),
	COUNT(col1)
FROM
	test_scn
GROUP BY
	ora_rowscn;

See the results. We just updated 1 row but ORA_ROWSCN got updated for 567 records. It means, in that particular block we have 567 records.
We cannot use this pseudocolumn in a query to a view but a table only. Though we can use it to refer to the underlying table while creating a view. We can also use this pseudocolumn in the WHERE clause of an UPDATE or DELETE statement.

If you have any questions/suggestions, please comment below or write me an email.

If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil Kumar
dbgeek.in

Sharing is caring!