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

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)
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google