Re: How to derive the username that owns the SQL in V$SQLAREA

On Dec 1, 7:38 pm, Peter Teoh <htmldevelo..._at_gmail.com> wrote:
> On Dec 2, 8:12 am, Peter Teoh <htmldevelo..._at_gmail.com> wrote:
>
>
>
> > I have given up. Anyone knows the answer?
>
> Sorry, I have found the answer from Oracle Forums:
>
> select username, sql_text
> from v$session s, v$sqlarea sql
> where s.sql_address = sql.address and s.sql_hash_value =
> sql.hash_value;
>
> It is also wrong. Every time a user login, the following will be
> added to the list (according to the above SQL):
>
> SELECT DECODE('A','A','1','2') FROM DUAL
>
> And all subsequently SQL issued by that user is not shown.
>
> So how to get the list of all SQL belonging to that user?

Peter, where possible Oracle shares SQL so a cached SQL statement not currently in use does not really have an owner. At any one time a session can be executing only one SQL statement though the session can have several open cursors. You can find open cursors via v $open_cursor. You already posted the sql for finding the current SQL statment for a session. You can use other columns in v$session to find the previous SQL statement.

If you want a history of all sql executed by a session have the session turn trace on when it starts and review the trace file.

The audit command can also be used in some cases to create a record of the sql ran by a specific user but you generally only want to do for a unique id that is used only by one person and probably has only one session.

Trace can be turned on from a second session for an already running session but previously executed SQL will be missing from the trace output.

HTH -- Mark D Powell -- Received on Sat Dec 01 2007 - 18:58:28 CST