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;
|22-05-19 11:27:04.000000000 PM||2000|
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;
|22-05-19 11:27:04.000000000 PM||1433|
|22-05-19 11:37:38.000000000 PM||567|
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.