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 .........
If its 8i and you have enabled archiving use LOGMINOR...
See my database is in nonarchivelog mode.So I cannot get any information from LOGMINOR.Is there any way by whcih I could find it out.Any data dictionary views can give any information.
no you cannot, imagine if oracle stores all dml operations in a table how big would your database be? HUGE!
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
Select 'Alter table '||Table_Name||' Monitoring ;' From User_Tables;
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.
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.