Need script to see DML/DDL activities in all tables
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Need script to see DML/DDL activities in all tables

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi,

    We are on Oracle 8.0.5.

    I am looking for some script which shows DML,DDL activities in the database within all tables/objects in a specific time frame.

    Example: Betw 2:00 PM to 3:00 PM, which are the tables in which DML transactions have been made. Also if possible, in terms of number of blocks/extents increased.

    Thanks,


    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Hmm, since you are on 8.0.5, you can't use logminer.

    You could set sql_trace on for the instance during that time frame. That might give you the DML you are looking for.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Aug 2001
    Posts
    184

    auditing

    cant u use auditing for this?
    OCP DBA 8i
    ocpwannabe@yahoo.com
    -----------------------------
    When in doubt, pick 'C'.

  4. #4
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Jeff,

    sql_trace is set to true in init.ora. Now, how can I check the DML out of it?

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  5. #5
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Re: auditing

    There are thousands of tables and I don't want to increase any other overhead due to Auditing.



    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  6. #6
    Join Date
    Feb 2001
    Posts
    389
    1) Enable Auditing - heavy overhead
    2) Enable SQL_trace - lots and lots of trace files.
    3) dump the redlog file /archive log file for the period.-- need to know internal structures.

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by samdba
    Jeff,

    sql_trace is set to true in init.ora. Now, how can I check the DML out of it?

    see steps 2 and 3 of this link: http://otn.oracle.com/docs/products/...4_str.htm#8760
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    dump the redlog file /archive log file for the period.-- need to know internal structures.

    - Infact, my original question is because, I want to know why lot of 100M Arch log files are getting generated. What makes these logs generated every 3-4 minutes inspite of low number of users and transactions.


    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  9. #9
    Join Date
    Feb 2001
    Posts
    389
    Then u should firstr run utlbstat.estat to find out the
    amount of redo generated,rollback generated , checkpoints done.

  10. #10
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    gpsingh,

    I have already done that.

    Is there any system table which stores block level information showing blocks inserted, updated, deleted for a particular table/index or datafile?
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

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