name: Mark D Powell
Author's Email: Mark.Powell@eds.com
written: 1 February 2002
Oracle version(s): 7.0 - 8.1.7
|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
for each row
v_last_modified_by varchar2(30) ;
v_last_modified_on date ;
from dual ;
:new.last_modified_by := v_last_modified_by ;
:new.last_modified_on := v_last_modified_on ;
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;
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.
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;
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.
See the DBA Administrators 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