Reading Time: < 1 minuteACTIVE session report :
set linesize 200
col Username for a10
col OSuser for a15
col Machine for a31
col Spid for a10
col Program for a30
select
S.Username,
S.OSuser,
S.Machine,
S.STATUS,
P.Spid,
S.Program,
S.SERIAL#,
S.SID
from V$PROCESS P, V$SESSION S
where P.Addr = S.Paddr
-- and s.Audsid > 8
order by S.STATUS;
set linesize 200
col Username for a10
col OSuser for a15
col Machine for a17
col Spid for a10
col Program for a30
spool sess.lst
select
S.Username,
S.OSuser,
S.Machine,
S.STATUS,
P.Spid,
s.process client_PID,
S.Program,
S.SERIAL#,
S.SID
from V$PROCESS P, V$SESSION S
where P.Addr = S.Paddr
-- and s.Audsid > 8
and s.process='&enter_process'
order by S.Username;
spool off
Active Session by SQL_ID:
set linesize 150
col Username for a10
col OSuser for a8
col Machine for a25
col Spid for a10
col Program for a30
select
S.Username,
S.OSuser,
S.sql_hash_value,
s.sql_id,
P.Spid,
S.Program,
S.SERIAL#,
s.status,
S.SID
,s.logon_time
,s.client_info
from V$PROCESS P, V$SESSION S
where P.Addr = S.Paddr
and s.Audsid > 8
Active Longops:
set linesize 150
col sql_text for a50
select sid, to_char(START_TIME,'MM-DD-YY:HH:MM:SS') , to_char(TIME_REMAINING,'MM-DD-YY:HH:MM:SS') , LAST_UPDATE_TIME, SQL_HASH_VALUE, sql_text
FROM v$session_longops a, v$sql b
WHERE username='SYSADM'
AND totalwork > 0
and sofar <> totalwork
and a.sql_hash_value=b.hash_value
and sid=&enter_sid
;
-- and s.Audsid > 8
Pull SQL info :
select sql_text,sql_id,hash_value from v$sqltext
where sql_text like '%&1%'
order by piece
/