How to derive the username that owns the SQL in V$SQLAREA
Peter Teoh
How do you derive the username of the session the created the SQL that
appears in V$SQLAREA?
In V$SQLAREA, there is a field SQL_ID.
Generally, I should look for a view that have SQL_ID, SERIAL# and SID
right? (or USERNAME and SQL_ID?)
I search through ALL the V$ tables - a few views seems plausible - V
$SESSION has a SQL_ID, but its does not match with any of those of V
$SQLAREA, and V$SESSION_LONGOPS has no rows in the view. And so is V
$SORT_USAGE.....
I have given up. Anyone knows the answer?
Peter Teoh
>
> 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?
Mark D Powell
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 --
Peter Teoh
>
> > 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!!!
Connor McDonald
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_...@yahoo.com
"Semper in excremento, sole profundum qui variat."
------------------------------------------------------------
Peter Teoh
>
> v$sql.parsing_user_id (joined to dba_users.userid) could be a
> reasonable approximateion
> --
Yes, I agree, tested for "scott" schema, that is quite a close
approximation. Any examples where parsing_user_id and execution user
id is not the same?