RE: How to find Procs run?

Scott,

Also look at:
http://www.ixora.com.au/scripts/library.htm

and see the script:
package_sql_executions.sql

Maybe it will do what you want - keep in mind the restrictions mentioned in the code description.
Also, I believe that 9i may have a view v$object_usage - according to Metalink
(http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_data
base_id=NOT&p_id=136642.1) it can contain index usage - maybe it contains package / procedure accesses as well.

Regards,
Bruce Reardon

-----Original Message-----
Sent: Saturday, 1 September 2001 4:00

Scott,

Try turning on auditing, as in 'audit execute on <procedure> whenever successful'

Dick Goulet

____________________Reply Separator____________________
Author: Scott Canaan <srcdco_at_ritvax.isc.rit.edu>
Date:       8/31/2001 9:04 AM

    First, I will say that I will contribute something to keep this list alive. I have benefitted way more from the list than I have given.

    With that said, I have a request from a customer to do a weekly report on which stored procedures are run and by whom. I have been looking in v_$sql, v_$sqlarea, v_$session, etc. and am having some limited success. The problem is that Oracle doesn't seem to keep any history of what happened in the past, only what the system looks like now. I also tried to create a table to hold the information and have a trigger on one of these views (I also tried the underlying tables), but Oracle won't let me create a trigger on any of these.

    I don't know where else to go, and I hate to tell a customer that I can't do it, but I'm out of ideas. Anyone here have any?

Thank you,

Scott Canaan (srcdco_at_rit.edu)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon_at_comalco.riotinto.com.au

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: ListGuru_at_fatcity.com (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

also send the HELP command for other information (like subscribing).