Check Last Insert Update Merge Time DML Time for each row of a table

by Snehashish Ghosh

Check Last Insert Update Merge Time Data Manipulation Language Operation Time for each row of a table


 Scenario


How to check Last Insert Update Merge Time i.e. Data Manipulation Language Operation Time for each row of a table. The application table does not have a audit column for timestamp and application code cannot be changed due to absence of budget and time.  

Solution


The requirement is to find out the last insert update timestamp for each row of table. Auditing of DML operation on the table cannot be enabled due to performance overhead. Also new column on the table cannot be added to track last update and insert timestamp. Crawl the archive log files using logminer cannot be done as developers are not used to the tool. The solution is to use  pseudo column  ORA_ROWSCN associated with each table row.

Pre-Requisite


 Create the table with ROWDEPENDENCIES. The  ROWDEPENDENCIES clause specifies whether table will use row-level dependency tracking.

With ROWDEPENDENCIES feature, each row in the table has a system change number (SCN) which represent a time greater than

or equal to the commit time of the last transaction that modified the row. The  ROWDEPENDENCIES setting cannot be changed after table is created

The default characteristics of a  create table is to create table with NOROWDEPENDENCIES if nothing is specificed in the create table statement.

The standard oracle alter statement cannot be used to modify the table attribute to have ROWDEPENDENCIES  settings enables if it is not done during create table statement.

Query

The following query can be used:

SQL> create table test(t1 number) ROWDEPENDENCIES;

Table created.

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select ora_rowscn,t1 from test;

ORA_ROWSCN T1
———- ———-
1517383 1

SQL> select scn_to_timestamp(1517383) from dual;

SCN_TO_TIMESTAMP(1517383)
—————————————————
09-JUN-13 09.11.51.000000000 AM

SQL> col last_modified_time format a50
SQL> select scn_to_timestamp(ora_rowscn) last_modified_time,t1 from test;

LAST_MODIFIED_TIME T1
————————————————– ———-
09-JUN-13 09.11.51.000000000 AM 1

SQL> insert into test values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select scn_to_timestamp(ora_rowscn) last_modified_time,t1 from test;

LAST_MODIFIED_TIME T1
————————————————– ———-
09-JUN-13 09.11.51.000000000 AM 1
09-JUN-13 09.14.58.000000000 AM 2

If you need last modified timestamp  for the entire table then max(ora_rowscn) can be used as below:

SQL> select scn_to_timestamp(max(ora_rowscn)) last_modified_time from test;

LAST_MODIFIED_TIME
————————————————–
09-JUN-13 09.14.58.000000000 AM

This ora_rowscn feature can be use from Oracle version 10g onwards.

In case the table for which insert update timestamp need to be tracked and during initial plan and design phase ROWDEPENDENCIESclause

has not been included, we need migrate the table data with a newly created table with same structure and  ROWDEPENDENCIES enabled.

You can use online table redifinition feature to restructure the table while the table is online and used. The create table as select * or CTAS can also be used if downtime for the specific table can be taken for the entire table migration duration.

Conclusion


Plan the table creation with ROWDEPENDENCIES feauture  enabled if application need to track the last modified timestamp based on last commit time of the transaction.

 

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

*