Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
ORA_ROWSCN returns the SCN information for the last modified date of the row for each row in a table. You can use this pseudo-column to determine last update date of rows approximately.
CREATE TABLE By Specifying ROWDEPENDENCIES
For row-based change tracking, the relevant table should be created by specifying ROWDEPENDENCIES. If not specified, tables are created as NOROWDEPENDENCIES. Each row in tables created by specifying ROWDEPENDENCIES will contain an extra 6 bytes of data.
1 | SQL> create table a(id number,ad varchar(50)) ROWDEPENDENCIES; |
Query ORA_ROWSCN
ORA_ROWSCN information of the rows can be queried as follows.
1 2 3 4 5 6 7 8 | SQL> select to_char(ORA_ROWSCN),ID from aduruoz.a; TO_CHAR(ORA_ROWSCN) ID -------------------------- ------- 1301894904749 1 1301894900367 2 1301894900627 3 1301894903961 4 |
Query Last Updated Date of a Table
The largest ORA_ROWSCN value will give you the last modified date.
1 2 3 4 5 | SQL> select max(to_char(ORA_ROWSCN)) from aduruoz.a; MAX(TO_CHAR(ORA_ROWSCN)) ------------------------------------------------------------------------------------------------------------------------ 1301894904749 |
Convert ORA_ROWSNC to Date
You can convert ORA_ROWSCN to date with the command below.
1 2 3 4 5 | SQL> select scn_to_timestamp(1301894904749) as timestamp from dual; TIMESTAMP --------------------------------------------------------------------------- 28-MAY-19 02.36.32.000000000 PM |
