Pseudocolumn – ORA_ROWSCN

Pseudocolumn - ORA_ROWSCN

admin DataBase , , ,

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.

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.

SCN_TO_TIMESTAMP(ORA_ROWSCN)COUNT(COL1)
22-05-19 11:27:04.000000000 PM2000

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.

SCN_TO_TIMESTAMP(ORA_ROWSCN)COUNT(COL1)
22-05-19 11:27:04.000000000 PM1433
22-05-19 11:37:38.000000000 PM567

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!

You May Also Like..

WHERE

SQL – WHERE clause

Hi DBgeek Army, Welcome back to the world of Oracle! Where clause helps to select desired rows or helps to […]

comments

Comment in SQL and PL-SQL

Hi DBgeek Army, Welcome back to the world of Oracle! Comments are very important aspects of our code. No matter […]

datafile

Oracle-Datafile

Hello guys, Hope you are doing fantastic being an Oracle professional. I wrote a blog on Oracle TABLESPACE. If you […]

2 Comments

  1. Suppose I inserted 5 rows into table something like

    sql > insert into t1 values ( 1,’a’);
    sql> insert into t1 values ( 2,’b’);
    sql> insert into t1 values ( 3,’c’);
    sql > commit;
    sql> insert into t1 values ( 4,’d’);
    sql > commit;
    sql> insert into t1 values ( 5,’e’);

    Now my question is how to find at what time first 3 insert statement executed.
    and Similarly for 4th and 5th insert statement executed.

    1. Unfortunately, this Pseudocolumn gets updated for the whole block but not for each row. I would suggest you to create your tables with row-level dependency to get row-level tracking. Search for NOROWDEPENDENCIES and ROWDEPENDENCIES

      thanks

Leave a Reply

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