Script to Find out SQL Text from Oracle 11g, 10g RAC Database Using SID, SPID

advertisements

_____________________________________________________________________________________________________________________

Many times DBAs asked to check the queries running from a particular session. If we have TOAD or SQL Developer kind of software, it is easy to pull it. Otherwise you can use the following script find out the sql text. SID (marked in red) number needs to be modified as per your requirement.

For RAC

set lines 120

set pages 200

SELECT     S.SID,S.USERNAME, s.inst_id, T.SQL_TEXT

FROM gV$SQLTEXT T, gV$SESSION S

WHERE      S.SQL_ADDRESS=T.ADDRESS

AND  s.sid in(448)

ORDER BY S.SID, T.PIECE;

For Single Instance

Example

SQL>

SELECT     S.SID,S.USERNAME,T.SQL_TEXT

FROM       gV$SQLTEXT T, gV$SESSION S

WHERE      S.SQL_ADDRESS=T.ADDRESS

AND        s.sid in(448)

ORDER BY S.SID, T.PIECE;

       SID USERNAME                       SQL_TEXT

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

       448 SYS                            SELECT  S.SID,S.USERNAME,T.SQL_TEXT FROM  gV$SQLTEXT T, gV$SESSI

       448 SYS                            ON S WHERE   S.SQL_ADDRESS=T.ADDRESS AND s.sid in(448) ORDER BY

       448 SYS                            S.SID, T.PIECE

How to Find Out Oracle Session details using SPID?  Relation between SID, SERIAL# and SPID?

Below mentioned query you can use to find out the session details from the Oracle Database.

For RAC

SELECT s.sid, s.serial#, s.inst_id, s.program

FROM   gv$session s , gv$process p

WHERE  p.addr =s.paddr

AND    p.spid in (14989)

For Single Instance

SQL> SELECT s.sid, s.serial#, s.program

FROM   gv$session s , gv$process p

WHERE  p.addr =s.paddr

AND    p.spid in (14989);

Examples

SQL> SELECT s.sid, s.serial#, s.program

FROM   gv$session s , gv$process p

WHERE  p.addr =s.paddr

AND    p.spid in (14989);

       SID    SERIAL# PROGRAM

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

       448      23285 sqlplus(TNS V1-V3)

SQL> SELECT s.sid, s.serial#, s.inst_id, s.program

FROM   gv$session s , gv$process p

WHERE  p.addr =s.paddr

AND    p.spid in (14989)  2    3    4  ;

       SID    SERIAL#    INST_ID PROGRAM

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

       448      23285          1 (TNS V1-V3)

_____________________________________________________________________________________________________________________

Website Stats