Is there a way to get the Date/Time when a row was last updated ?

The Oracle (tm) Users' Co-Operative FAQ

Is there a way to get the Date/Time when a row was last updated?

Back to index of questions

As Oracle is configured out of the box there is a very quick short answer to this question: NO.  For Oracle to keep track of this information on a per row basis would require a lot of overhead, which for the great majority of installations would be unnecessary and therefore wasteful.

However, if you really need this information here are some possible ways of trapping this information.

1 – Use triggers to capture this information on a per table basis

2 - Use the Oracle audit command to capture changes

3 - Search the Oracle redo logs for the information using the log miner utility

Pros and Cons of each method

1 – The Pro is this adds overhead only for critical to monitor objects

    The Con is this requires coding but it is fairly straightforward

2 – The Pro is this is relatively easy to set up, as it is a built-in feature

    The performance overhead is usually not that noticeable

    The Con is that by default the audit table is stored in the system tablespace

    leading to the possibility of filling the system tablespace as auditing can be

    very costly from a storage standpoint

3 – The Pro is there is no pro in my opinion to this approach; however if audit triggers

    and/or the Oracle auditing function are not in use then for something that happened 

    recently that you really need to attempt to find, then this option is available

    The Con is this is a resource, time intensive approach 

How do you use triggers to capture this information?  Here is an example:

The basic procedure is to modify the table adding a last_modified_by and last_modified_on column.  Then place before insert and before update triggers on the table.  This will allow trapping the Oracle user id and date/time of the row creation and of any updates.  Here is a working example trigger.  MARKTEST can be any table that has the two auditing columns defined previously defined, just change the table name.  Alternately a separate history table could be used to hold the audit data.

set echo on

create or replace trigger marktest_biu

before insert or update

on marktest

for each row

--

declare

--

v_last_modified_by     varchar2(30) ;

v_last_modified_on     date         ;

--

begin

--

select

  user

 ,sysdate

into

  v_last_modified_by

 ,v_last_modified_on

from dual ;

--

:new.last_modified_by := v_last_modified_by ;

:new.last_modified_on := v_last_modified_on ;

--

end ;

/

The sys_context function is a valuable potential source of information for auditing purposes especially if you have applications with imbedded Oracle user id and passwords.

Using the audit command: 

Table MARKTEST is created then object level auditing is set using the following command

> audit insert, update, delete on marktest by access;

Audit succeeded.

I attempted to create the table (again), access is attempted from an ID without proper privilege to the table, and then the table is updated from a user with insert privilege, the DBA_AUDIT_TRAIL is queried, and finally auditing is turned off.  There is a great deal more information available than shown below.

> l

  1  select username, timestamp, action_name, returncode

  2  from dba_audit_trail

  3* where obj_name = 'MARKTEST'

> /

USERNAME                       TIMESTAMP ACTION_NAME                 RETURNCODE

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

MPOWEL01                       01-FEB-02 CREATE TABLE                       955  -- table already existed

TESTID                         01-FEB-02 INSERT                            2004  -- 00942 issued to user

MPOWEL01                       01-FEB-02 INSERT                               0  -- insert successful

> noaudit insert, update, delete on marktest;

Noaudit succeeded.

Warning the auditing information is kept by default in the system tablespace and by access (row level) auditing can generate huge amounts of data very quickly possibly impacting the ability of Oracle to function. 

Data Miner is a topic by itself and I will not attempt to cover it here.

Further Reading:

See the DBA Administrator’s Guide and SQL manual for information related to auditing and SQL syntax.

If you have Oracle metalink support then you can see How To Set Up Auditing Doc Id: 1020945.6 which will reference several other documents that may be of assistance such as Auditing DML (Insert, Update, Delete) Doc Id: 130146.1

Back to top

Back to index of questions