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

Peter Teoh wrote:
>
> 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.
>
> a. Login as SCOTT. Dont do anything else.
> b. 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!!!

v$sql.parsing_user_id (joined to dba_users.userid) could be a reasonable approximateion

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------