rem *********************************************************************
rem * Copyright © Oracle-Consultant.co.uk 2001, all rights reserved     *
rem *                                                                   *
rem * Name      : cursta.sql                                            *
rem * Synopsis  : Shows current statements for active sessions          *
rem * Source    : http://www.oracle-consultant.co.uk                    *
rem *                                                                   *
rem * Oracle-Consultant.co.uk are not responsible for any liability     *
rem * that may arise from the use of this code. Support can be obtained *
rem * by emailing script_support@oracle-consultant.co.uk                *
rem * Note: This script is best viewed in a fixed-width font.           *
rem *********************************************************************
 
column pu   format a8       heading 'O/S|Login|ID'
column su   format a8       heading 'Oracle|User ID'
column stat format a8       heading 'Session|Status'
column ssid format 999999   heading 'Oracle|Session|ID'
column sser format 999999   heading 'Oracle|Serial|No'
column spid format 99999999 heading 'O/S|Process|ID'
column txt  format a2000    heading 'Current Statement' wrapped

set linesize  1000
set pagesize  32000
set trimspool on

select   p.username                 pu
,        s.username                 su
,        s.status                   stat
,        s.sid                      ssid
,        s.serial#                  sser
,        substr(p.spid,1,8)         spid
,        substr(sa.sql_text,1,2000) txt
from     v$process p
,        v$session s
,        v$sqlarea sa
where    p.addr           = s.paddr
and      s.username         is not null 
and      s.sql_address    = sa.address(+) 
and      s.sql_hash_value = sa.hash_value(+)
and      s.status         = 'ACTIVE'
order by 1,2,7 
/ 
 
column pu   clear
column su   clear
column stat clear
column ssid clear
column sser clear
column spid clear
column txt  clear