oracle-session-查看当前非空闲用户会话信息

prompt 查看当前非空闲用户会话信息
set linesize 1000
set pagesize 1000
col sid for 99999
col spid for a18
col event for a50
col module for a35
col machine for a35
col username for a20
col holder for a20
col final for a20
col sql_id for a20
col exec_gets for 99999999
col seconds for a15
col object_id for 99999999
col param for a30
col sql_text for a110
col PGA_USE for 9999
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select a.sid,
       a.serial#,
       a.username,
       a.machine,
       a.module,
       a.event,
       a.sql_id,
       executions,
       round(decode(c.executions,0,buffer_gets,buffer_gets/executions)) exec_gets,
       a.ROW_WAIT_OBJ# object_id,
       a.BLOCKING_INSTANCE||'_'||a.blocking_session  holder,
       a.FINAL_BLOCKING_INSTANCE||'_'||a.FINAL_BLOCKING_SESSION final,
       to_char(LAST_CALL_ET) seconds,
       a.p1 || '_' || a.p2 || '_' || a.p3 param,
       b.spid,
       trunc(b.PGA_USED_MEM / 1024 / 1024,2) as PGA_USE_mb,
       substr(c.sql_text,0,100) sql_text
  from v$session a, v$process b,v$sql c
 where a.paddr = b.addr(+)
   and a.status = 'ACTIVE'
   and not (a.type = 'BACKGROUND' and a.state = 'WAITING' and
        a.wait_class = 'Idle')
   and a.sql_id=c.sql_id(+)
   and a.sql_child_number=c.CHILD_NUMBER(+)
 order by a.sql_id, a.machine
/