Link to home
Start Free TrialLog in
Avatar of _Paul
_PaulFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Monitor table usage in oracle 10g without monitoring the indeces

I want create a report on what tables are used in my database to eliminate the tables which are never accessed used.

i found this article which does this task but it mnitors the index(es) on each table

http://wiki.oracle.com/page/Monitor+Index+Usage

But in my problem I have to monitor the table access/usage by monitoring the columns...

i am using Oracle 10g with ~1100 tables
Avatar of mrjoltcola
mrjoltcola
Flag of United States of America image

I'll say right away that I am not aware of a table that has LAST ACCESS time of a table, but I welcome another expert to enlighten me. DBA_TAB_MODIFICATIONS does log DML, but a SELECT is not DML.

What you CAN do is to use 10g Fine Grained Auditing and set a policy on all those tables to log SELECT. Then run a report at the end of 30 days, or however long, and you'll see who is accessing what.
Avatar of schwertner
Oracle doesn't keep explicit data about the usage of the tables.
If you need this you can create triggers to monitor DML (DELETE, INSERT, UPDATE)
activities, but there is no possibility to monitor SELECT access.

There is Fine Grained Audit fature:

9i  FGA provides support for SELECT statements only.
10g FGA extends in the following ways:

    --> Support for DML statements :
        A. INSERT
        B. UPDATE
        C. DELETE
Auditing is turned off by default in Oracle, so unless you (or some DBA at your organization in the past) has turned on auditing, your audit_trail will be empty!

The audit records can either be written to an ASCII log file on the server, or they can be written to the database where they are visible in the view: DBA_AUDIT_TRAIL.  This is controlled by the init parameter: "AUDIT_TRAIL" that can be set or changed in your init*.ora file or spfile.
When you say you "have to monitor the table access/usage by monitoring the columns..." do you mean you need to identify when data changes in each column of every table?

If so then you could turn on auditing or build your own triggers to gather the required information about which columns have changed.  Turning on auditing will be an overhead to the running of your database and will potentially generate lots more data that will need to be managed.
ASKER CERTIFIED SOLUTION
Avatar of mrjoltcola
mrjoltcola
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial