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