Solved

Monitor table usage in oracle 10g without monitoring the indeces

Posted on 2009-04-09
4
3,030 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:_Paul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24107946
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.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24107972
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.
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 24107981
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.
0
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 500 total points
ID: 24108077
Don't use triggers for this, use FGA.

Using triggers is less desirable as it can affect the actual transactions if you have errors. Use FGA feature, thats what is for. You don't even need the normal auditing configured, but you do need to set "audit_trail" to db_extended:

See below my example using the SCOTT EMP sample schema.



-- Sample 10g Fine Grain Auditing policy setup
-- mrjoltcola
-- Use the EMP schema (SCOTT/TIGER) to demonstrate fine-grained auditing (@{ORACLE_HOME}/rdbms/admin/utlsampl.sql)
 
-- 1st check database to see if either AUDITING and/or FGA support is setup
 
show parameter audit
 
/*
Should see:
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\ADMIN\DEV\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      NONE
*/
 
-- We don't have to have standard auditing on, but to use standard auditing, set AUDIT_TRAIL above
-- Change to db_extended so we get fine grained audit plus SQL_BIND (bind vars) and SQL_TEXT auditing. Then restart database
 
sqlplus / as sysdba
 
SQL> ALTER SYSTEM SET audit_trail=db_extended SCOPE=SPFILE;
SQL> shutdown immediate
SQL> startup open
 
 
-- To use FGA, we use FGA policies
 
-- FGA
-- Add a SALARY policy to the EMP table
-- Run as SYS or another user with execute on package dbms_fga
-- Default action is to audit SELECT but with 10g we can add DML actions by adding to statement_types parameter
-- Setup a policy to audit access of Employee Salaries
begin
   dbms_fga.drop_policy (
      object_schema=>'SCOTT',
      object_name=>'EMP',
      policy_name=>'EMP_ACCESS'
  );
 
   dbms_fga.add_policy (
      object_schema=>'SCOTT',
      object_name=>'EMP',
      policy_name=>'EMP_ACCESS',
      audit_condition => NULL,  -- TRUE
      audit_column    => 'SAL',
      statement_types => 'SELECT,INSERT,UPDATE,DELETE'
);
end;
/
 
 
connect scott/tiger
 
-- Scott selects salaries and then gives self a raise
select * from emp;
update emp set sal = 30000 where ename = 'SCOTT'; 
 
 
-- DBA connects and checks audit trail
connect / as sysdba
 
-- Find who ran what against EMP table
select timestamp, db_user, sql_text from dba_fga_audit_trail where object_name = 'EMP';
 
/*
01-APR-09          SCOTT	select * from emp
 
01-APR-09          SCOTT	update emp set sal = 30000 where ename = 'SCOTT'
*/

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question