Home » RDBMS Server » Performance Tuning » Session related Quries
Session related Quries [message #65815] Tue, 04 January 2005 00:18 Go to next message
Velu Natarajan
Messages: 16
Registered: July 2002
Junior Member
Dear GURU's,

Can any one tell me how to find SQL statements by specific session
(using session id / not by using parsing_user_id)

Thanks & Regards,

Velu N
Re: Session related Quries [message #65823 is a reply to message #65815] Tue, 04 January 2005 22:38 Go to previous message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Hi,

Oracle associates sessions with statements for open cursors (V$OPEN_CURSOR), but also keeps track of the last and current SQL statement for each session (V$SESSION.SQL_ID and PREV_SQL_ID).

Here are a couple of queries you can try:

Session 1:
SQL> SELECT USERENV('SESSIONID') FROM dual;

USERENV('SESSIONID')
--------------------
                  97


Session 2:
SQL> SELECT sql_text AS "OPEN CURSORS"
  2    FROM v_$open_cursor o, v_$session s
  3   WHERE o.saddr     = s.saddr
  4     AND s.audsid = 97
  5  /

OPEN CURSORS
------------------------------------------------------------
SELECT USERENV('SESSIONID') FROM dual


SQL> SELECT sql_text AS "LAST SQL EXECUTED"
  2   FROM v$sqltext t, v$session s
  2  WHERE  s.audsid = 97
  3    AND  s.sql_id = t.sql_id
  4  /

LAST SQL EXECUTED
----------------------------------------------------------------
SELECT USERENV('SESSIONID') FROM dual


Best regards.

Frank
Previous Topic: Oracle 8/9/10 performance problem
Next Topic: how to make sql&pl/sql mor efficient
Goto Forum:
  


Current Time: Thu Mar 28 03:54:07 CDT 2024