Re: Tracking Procedures

From: <amerar_at_iwc.net>
Date: 15 May 2007 12:03:10 -0700
Message-ID: <1179255790.263116.99770@q75g2000hsh.googlegroups.com>

On May 15, 1:51 pm, fitzjarr..._at_cox.net wrote:
> On May 15, 12:37 pm, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
>
> > Hi,
>
> > We have a database with over 2000 stored procedures. We're pretty
> > sure not all of them are used. Short of turning on tracing for a few
> > weeks, or trying to use TOAD or something to piecemeal everything
> > together, does Oracle have any way of storing the last time a
> > procedure was executed??
>
> > Thanks!
>
> Not directly. You might feel energetic and decide to wade through the
> entries in V$SQL or V$SQLAREA to find calls to said procedures. You
> could also modify each and every one of them to write to a usage table
> so you could query the results. Then, you've already mentioned using
> 10046 trace output to discover this. That is probably your best bet.
>
> David Fitzjarrell

What about a script to parse v$sql every 3 minutes to get the names of the stored procedures that are running or have run, then I can trace it that way........

I assume that entries stay in v$sql for a bit of time once they are done running??? Received on Tue May 15 2007 - 14:03:10 CDT