REM 
REM	file: CURusers.sql 
REM 
REM	This SQL*Plus script helps identify users currently        
REM     connected to the database and the statement they 
REM     are currently running. 
REM 
REM	11-JUL-96	Created.	   	Mike M.	 
 
SET PAGESIZE 999;
SET LINESIZE 132;

spool curusers.lis;
set termout off
break on today 
column today new_value _date
select to_char(SYSDATE, 'fmMonth DD, YYYY') today from dual;
clear breaks
set termout on
 
column pu format a15 heading 'O/S|Login|ID' justify left 
column su format a15 heading 'Oracle|User ID' justify left 
column stat format a10 heading 'Session|Status' justify left 
column ssid format 999999 heading 'Oracle|Session|ID' justify right 
column sser format 999999 heading 'Oracle|Serial|No' justify right 
column spid format 999999 heading 'O/S|Process|ID' justify right 
column txt format a50 heading 'Current Statment' justify center word 
 
select p.username pu,  
       s.username su,   
       s.status stat, 
       s.sid ssid, 
       s.serial# sser, 
       lpad(p.spid,7) spid, 
       substr(sa.sql_text,1,540) 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(+) 
order by 1,2,7 
/ 
 
column pu clear  
column su clear  
column stat clear  
column ssid clear  
column sser clear  
column spid clear  

spool off;

exit