Re: Get all sql's that a user has executed

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sun, 16 Sep 2001 22:12:52 +0200
Message-ID: <tqa355d6ak7af2@news.demon.nl>

"MK" <michael.kurz_at_c-plan.com> wrote in message news:9o2jnn$2dum$1_at_news.okay.net...
> Hello
>
> I know that with the V$sqlarea I can get all sql's that are executed.
>
> Problem 1:
> If the sql is executted twice I only see the execution time of the first
> execution.
>
> Problem 2:
> I can not see which Session executed the sql.
>
> Is there a select where I can get the session an the real History
> of the executions with all sql's (the one who are twice too).
>
> Many thanx for help
> Micha
>
>
>
>
>
>

As you have the sid of the session (or at least can derive it by means of v$open_cursor) I don't think problem 2 is a problem.

To resolve problem 1 you need to trace the session, there is no other solution.

You can format your trace file with tkprof's option 'aggregate=no', which will stop summarizing all executions. The default for aggregate is yes. The only other alternative is to audit all selects. That you don't want.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Sun Sep 16 2001 - 15:12:52 CDT