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

On Dec 2, 8:58 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
> > 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;
>

To emphasize what I am saying below - that the above SQL does not list the SQL issued by the user for that session, I did an experiment.

1. Login as SCOTT. Dont do anything else.
2. Login as SYS, run the above SQL. Total 4 SQL returned, only ONE of the username belongs to SCOTT, which is below:

> > SELECT DECODE('A','A','1','2') FROM DUAL
c. In the SCOTT run 10 SQL in a scripts. d. In SYS, it still returned 3 SQL - none of them belonging to SCOTT. No matter how many SQL SCOTT runs, it is not listed as the output.
e. But in V$SQLAREA, I did managed to find ALL the SQL issued by SCOTT. Owner is not retrievable.

> 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.

THank you for that, I learned something.

>You already posted the sql for finding the current SQL
> statment for a session.

I think this statement is in error.

> You can use other columns in v$session to
> find the previous SQL statement.
>

Erh....don't know how.....join via sql_id, or sid, or serial#, or what?

> 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.
>

Thanks, 5 googlegroup stars for that answer.

> 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.

Another 5 googlegroup stars for that answer.

>
> 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.

Thanks, 5 googlegroup stars for that answer.

Overall, I think the answer is resolved - not possible to retrieved the ownership, exactly like you mentioned.

Thanks!!! Received on Sun Dec 02 2007 - 03:12:17 CST