How To Find the Last Modified Date of a Table in Oracle

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.

Query ORA_ROWSCN

ORA_ROWSCN information of the rows can be queried as follows.

Query Last Updated Date of a Table

The largest ORA_ROWSCN value will give you the last modified date.

Convert ORA_ROWSNC to Date

You can convert ORA_ROWSCN to date with the command below.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

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