Last time a table was mod via DML?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Last time a table was mod via DML?

  1. #1
    Join Date
    Nov 2001
    Location
    New Brunswick, NJ
    Posts
    67

    Smile

    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

  2. #2
    Join Date
    Sep 2001
    Posts
    30
    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 -
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  4. #4
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    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.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    alter table yourtable monitoring then look user_tab_modifications for last DML on that table

  6. #6
    Join Date
    Nov 2001
    Location
    Miami, FL, USA
    Posts
    1

    Smile Last time a table was mod via DML?

    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

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