DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Last DML Date

  1. #1
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530

    Thumbs down

    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 .........


    Regards,
    Rohit Nirkhe

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    If its 8i and you have enabled archiving use LOGMINOR...
    Reddy,Sam

  3. #3
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    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.

    Regards,
    Rohit Nirkhe

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    no you cannot, imagine if oracle stores all dml operations in a table how big would your database be? HUGE!

  5. #5
    Join Date
    Feb 2001
    Posts
    184
    Hi Rohit,

    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

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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 Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width