Query the Process ID and SQLTEXT area

Here's how to view the details of each session and the I/O stats per session, then use one of the SIDs to query any SQL held in the SQLAREA. The output is spooled to a file.

The ideas for this script are ages old, and bits come from several sources including myself. I have embellished various parts to make them more presentable. I don't think anyone can claim ownership of this sort of stuff anyway.

               
set linesize 200 
set pagesize999 
set verify off 
set feed off 
set termout on 
set echo off 
set heading off 
clear screen 

select  'Snapshot Date: '||to_char(sysdate,'MM/DD/YY HH:MI:SS') 
from    dual; 

select  'Database: '||name 
from    v$database; 

column a        format 999              heading 'SID' 
column b        format 99999999         heading 'Serial #' 
column c        format a9               heading 'OS-User' 
column d        format 99999999         heading 'OS-Pid' 
column e        format a8               heading 'Status' 
column f        format 9999             heading 'Command' 
column g        format a11              heading 'Schema User' 
column h        format a10              heading 'Sess-Type' 
column i        format a10              heading '%Hit-Ratio' Jus c 
column j        format 99990.99         heading 'CPU' jus c 
column k        format 99999999         heading 'Block changes' 
column l        format 99999999         heading 'Cnstnt changes' 
column n        format 99999999999      heading 'Hash Value' 

set heading on 

select  substr(s.sid,1,3) a, 
        substr(s.serial#,1,8) b, 
        substr(s.osuser,1,9) c, 
        p.spid d, 
        substr(s.status,1,8) e, 
        substr(s.command,1,4) f, 
        substr(s.schemaname,1,11) g, 
        substr(s.type,1,10) h, 
        substr(decode( (i.consistent_gets + i.block_gets), 
                       0,'None', 
                        (100*(i.consistent_gets + 
                         i.block_gets - i.physical_reads)/(i.consistent_gets +
                         i.block_gets))),

1,4) i, 
        t.value j, 
        substr(i.block_changes,1,8) k, 
        substr(i.consistent_changes,1,8) l, 
        s.sql_hash_value n 
from    v$process p, 
        v$sesstat t, 
        v$sess_io i , 
        v$session s 
where   i.sid=s.sid 
and     p.addr=paddr(+) 
and     s.sid=t.sid 
and     t.statistic#=12 
/ 

set heading off 

column dt new_value day_time noprint 

select  to_char(sysdate,'MM_DD_YY_HH:MI:SS') dt 
from    dual; 

set termout on 
set verify off 
set feedback off 
set linesize 100 
set pagesize  999 
set heading on 

ttitle off 
btitle off 

set heading off 

prompt 
prompt "Enter the SID to query :"&&sid 

column ad noprint 
column bval noprint 
column cpce noprint 

spool vo_query_sqltext.&day_time..out 

select  a.ADDRESS ad, a.HASH_VALUE bval,  a.PIECE cpce, a.sql_text 
from    v$sqltext_with_newlines a, 
        v$session b 
where   a.address = b.sql_address 
and     a.hash_value = b.sql_hash_value 
and     b.sid = &&sid 
order by  1,2,3,4 
/ 
spool off 

prompt  
prompt  
prompt * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *  * 
prompt 
prompt 
prompt SEE FILE: vo_query_sqltext.&day_time..out 

undefine sid 

set pagesize 56 
set linesize 80 

For More Information

• What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.