Re: tables

From: Jared Still <jkstill_at_gmail.com>
Date: Tue, 14 Apr 2009 11:07:09 -0700
Message-ID: <bf46380904141107m4b612b26p547c3e7cdc884819_at_mail.gmail.com>

On Tue, Apr 14, 2009 at 10:41 AM, Zelli, Brian <Brian.Zelli_at_roswellpark.org>wrote:

>  Hello all,
>    Quick and extremely lazy question.  Is there a native way of seeing how
> often a table is accessed?  Or does someone have a script that will list
> most frequently used user tables?
>
>

audit select on OWNER.TABLE_NAME;

I've used the following view when reviewing auditing on tables:

create or replace view dba_table_audit_flags (

   owner
, table_name
, select_audit
, insert_audit
, update_audit
, delete_audit
, alter_audit
, audit_audit
, comment_audit
, grant_audit
, index_audit
, lock_audit
, rename_audit
, references_audit
, execute_audit

   --, audit_flags
)
as
select u.name, o.name

       , decode(substr(t.audit$,19,2),'--','N','Y') -- select
       , decode(substr(t.audit$,13,2),'--','N','Y') -- insert
       , decode(substr(t.audit$,21,2),'--','N','Y') -- update
       , decode(substr(t.audit$, 7,2),'--','N','Y') -- delete
       , decode(substr(t.audit$, 1,2),'--','N','Y') -- alter
       , decode(substr(t.audit$, 3,2),'--','N','Y') -- audit
       , decode(substr(t.audit$, 5,2),'--','N','Y') -- comment
       , decode(substr(t.audit$, 9,2),'--','N','Y') -- grant
       , decode(substr(t.audit$,11,2),'--','N','Y') -- index
       , decode(substr(t.audit$,15,2),'--','N','Y') -- lock
       , decode(substr(t.audit$,17,2),'--','N','Y') -- rename
       , decode(substr(t.audit$,23,2),'--','N','Y') -- references
       , decode(substr(t.audit$,25,2),'--','N','Y') -- execute
       --, t.audit$

from sys.user$ u, sys.tab$ t, sys.obj$ o where o.owner# = u.user#
  and o.obj# = t.obj#
  and (instr(t.audit$,'S') > 0 or instr(t.audit$,'A') > 0) /

create public synonym dba_table_audit_flags for sys.dba_table_audit_flags;

grant select on dba_table_audit_flags to dba;

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 14 2009 - 13:07:09 CDT