Lesson 36 : Pseudocolumn - ORA_ROWSCN

Pseudocolumn    -   ORA_ROWSCN

 Useful to find timestamp of last update of particular row in a table.

 

For each row in any table, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row.

This pseudocolumn is useful for determining approximately when a row was last updated. But, It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking.

Note :  We cannot use this pseudocolumn in a query to a view. However, We can use it to refer to the underlying table when creating a view. We can also use this pseudocolumn in the WHERE clause of an UPDATE or DELETE statement.

ORA_ROWSCN is not supported for Flashback Query. Instead, use the version query pseudocolumns, which are provided explicitly for Flashback Query.

Restriction: This pseudocolumn is not supported for external tables.

Example :

SQL> select ORA_ROWSCN,NAME FROM employee;

ORA_ROWSCN NAME

---------- ----------

1.2499E+13 John

1.2499E+13 Devy

1.2499E+13 Paul

1.2499E+13 Raj

1.2499E+13 Chetan

1.2499E+13 Jayne

1.2499E+13 Senthil

1.2499E+13 Jeya

8 rows selected.

SQL> select * from employee;

NAME          DEPT_ID LOCATION       SALARY

---------- ---------- ---------- ----------

John               10 Dallas          50000

Devy              10 Dallas          75000

Paul               20 Texas           85000

Raj                 20 Texas           65000

Chetan             30 Boston          95000

Jayne              30 Boston          55000

Senthil            30 Chennai         45000

Jeya               30 Chennai         25000

8 rows selected.

SQL> update employee set Salary=75000 where name='John';

1 row updated.

SQL> select sysdate from dual;

SYSDATE

--------------------
28-DEC-2012:09:36:52

SQL> commit;

Commit complete.

SQL> select NAME,ORA_ROWSCN,SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM employee where name='John';

NAME       ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)

---------- ---------- ---------------------------------------------------------------------------

John       1.2516E+13 28-DEC-12 09.36.54.000000000 AM