Last time a table was mod via DML?

[mrpaulwass]

Are there any Oracle tables which keep track of when a table was last updated via a DML statement?

I would love a select like

select table_name from user_tables
where last_dml_update_done > sysdate - 5.

I don't want to use auditing, and was just wondering if there are any internal Oracle tables that keep track of this.

I do have SYS_UPDATE_TIME on most of my tables, which does give me what I want. But it's the tables which don't have this column that I'm trying to work on.

Thanks,

Paul

[jason]

Unless you enable auditing, there is no way to know when a table was updated via a DML statement.

Thanks
Jason

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Resources ( http://dbresources.com)

- Online community for Oracle DBAs and Developers -
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[Shestakov]

Originally posted by mrpaulwass

I don't want to use auditing, and was just wondering if there are any internal Oracle tables that keep track of this.

Can u calculate size of oracle dictionary if :
application has 3 tables ~ 10 000 000 rows
avg update/delete operations 100 000 per day
can u estimate size of dictionary after 3 monthes?

I will be very wondering if Oracle do that?
This is will be "huge head ache" for DBA.

[Raminder]

You can trace the DML statements using LOGMINER and in 9i, the functionality of LOGMINER has been increased to a great extent. Simple SQL statement like you mentioned can be issued against redologs to know what changes were made to the database in the past.

[pando]

alter table yourtable monitoring then look user_tab_modifications for last DML on that table

[cjofre]

I would recommend LogMiner like Mr. Raminder said.
It will not cause you to create an over head on the day to day operations, and this type of "investigation" should rarely take place anyway. If you are trying to figure out a "who done it"! scenario where too many people have SQL access to the DB, then I would recommend tighteting your security and putting audit trails on applications. This is not what a DBA should be doing on a regular basis!

Good luck!
C. Jofre.
OCP DBA