Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: audits

RE: audits

From: Ruiz, Mary A (CAP, CDI) <>
Date: Thu, 20 Jul 2000 12:47:02 -0400
Message-Id: <>


   I recently set up auditing on a schema on a small datamart to find out who selects from the tables / views in it (less than 10 users, rather infrequently). Auditing records are written to the table sys.aud$ which resides in the system tablespace by default. It is authorized by Oracle to move this table to a different tablespace - I would recommend it if you plan to do some auditing. Check Metalink / Tales from the Script ,Problem ID 1019377.6

AUDIT SELECT ON EMP.empid,, emp.dept by access ; audit select on emp ;

The above will show any attempt to select data from emp, as per your spec.

Here is a script to show the auditing results: ( you should adjust the criteria to suit
your purpose)
select statement, userid, terminal, a.action,, obj$name, timestamp from sys.aud$ a, sys.audit_actions b
where userid not in ('SYS','SYSTEM')

and a.action = b.action
and a.action not in (100, 101,102)
and a.userid <> 'MRUIZ'

order by obj$name, timestamp

Here is another script:
col terminal format a10
col obj$name format a25
col userid format a8
col name format a12
accept From_date char prompt "Enter beginning date as MM/DD/RR : " select obj$name, name, terminal, userid, timestamp from sys.aud$ a, audit_actions b
where a.action = b.action
and a.timestamp >= TO_DATE('&From_date','MM/DD/RR') and userid not in ('SYS','SYSTEM')
and name not in ('LOGON','LOGOFF', 'LOGOFF BY CLEANUP') and terminal is not null
These are not beautiful but they worked for me.


Mary Ruiz
Atlanta GA

> -----Original Message-----
> From: Charlie Mengler []
> Sent: Thursday, July 20, 2000 10:16 AM
> To: Multiple recipients of list ORACLE-L
> Subject: audits
> 1) What is the SQL that will report all objects and actions that are
> being audited within any specific instance?
> 2) I have an instance where hundreds of users all login with the same
> Oracle username and can store Oracle Browser queries within the DB.
> This has resulted in thousands & thousands of views being stored
> within the DB. Is there a way to enable auditing against just
> views (owned by a specific schema)? If so, how? The idea being
> that after a few months of auditing I'll be able to generate a
> report of those views that have not been used & be able to delete
> them.
> Comments, ideas & suggestions are welcomed.
> --
> Charlie Mengler Maintenance Warehouse
> 5505 Morehouse Drive
> 858-552-6229 San Diego, CA 92121
> There is a fine line between vision & hallucination. Don't cross it!
> --
> Author: Charlie Mengler
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Thu Jul 20 2000 - 11:47:02 CDT

Original text of this message