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

Thread: Trace table usage

  1. #1
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Hi there,

    I was wondering if anyone could help.

    I have just been asked if there is any way to set up some sort of trace to see, for example all the tables which are being updated (selected from, ect) for a given period.

    I am running Oracle7.3 on WindowsNT.

    Sorry I'm not being more specific, the senior DBA is on holiday, I'm filling the gap.

    Many thanks

    Allie


  2. #2
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hello ally

    Hmm if i understand you clearly i think we are talking about monitoring the table if so then

    To automatically gather statistics for a particular table, enable the monitoring attribute using the MONITORING keyword. This keyword is part of the CREATE TABLE and ALTER TABLE statement syntax.

    After it is enabled, Oracle monitors the table for DML activity. This includes the approximate number of inserts, updates, and deletes for that table since the last time statistics were gathered. Oracle uses this data to identify tables with stale statistics.

    View the data Oracle obtains from monitoring these tables by querying the USER_TAB_MODIFICATIONS view

    There may be a few hours delay while Oracle propagates information to this view

    i hope i understood ur requirements clearly ;-)

    regards
    Hrishy

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I dont think monitor works in 7.3

  4. #4
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Thanks all,

    But I was thinking of monitoring the entire database, to see which tables were being updated (not so much stats on one particular table)

    We are using PeopleSoft which runs a large number of scheduled Jobs throughout the day, the trace would be used to understand which particular tables/views ect were being touched over a given period.

    Hope this clears things up

    Allie

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hello Ally

    You got to look into auditing options if you are on 7.3 some examples are like

    AUDIT SELECT TABLE, UPDATE TABLE
    BY scott, blake;

    also you need to specify where you need to keep the audit trail wheather in db or os in the init.ora file infortunately this metahod requires a restart of db .

    change the initsid.ora parameter audit-trail=db

    auditind info would be available in sys.aud$ beawre this can fill up the system tablespace unless you dont truncate it periodically.

    example are

    audit all
    on scott.emp
    whenever not sucessful

    audit all
    on scott.emp
    whenver sucessful.

    you must run cataudit.sql ORACLE_HOME/rdbms/admin as sys to create the views sys.aud$

    please also modify the cataudit.sql script so that the tablespace is something else other then sys.

    hey pando tahnkx i missed ally was on 7.3 sorry ally

    regards
    Hrishy

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