If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Hi,
This is Rohit Nirkhe.Can anyone tell me how I can find when the last DML statement was performed on a table.I know that by auditing we can do it,but I have not enabled auditing.
I have around 2500 tables in my database out of which only 1500 tables are pertaning to my application.Other tables I want to drop.
Let me know .........

There is a way to find the last dma time and even It can Tell you what was the DML.

Alter Table Tabname Monitoring;

Once this is there, Select * from User_Tab_Modifications;

If you want all tables to be Checked, Run the script like this
Spool Monitor.sql
Select 'Alter table '||Table_Name||' Monitoring ;' From User_Tables;
Spool Off;
@Monitor.sql

Now You will be getting the results. If you analyze those tables, Monotoring Statistics will be gone. If want to check if any table is under Monitoring Check From Dba_Users.
Hope this may help you.
Thanks

Well, setting the audit for a table is much more appropriate than turning monitoring on if the main purpose is to record the last DML time for the particular table. The purpose of monitoring is to help optimizer get more acurate information in the time between two subsequent analyze processes and hence brings much more overhead compared to auditing.

If you want to know the last DML time for the table, turning auditing on for that table is the most efficient and "natural" way to go. And besides, monitoring is available only in 8i, while auditing is available for more than a decade now.

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?