Search This Blog

Total Pageviews

Thursday, 1 September 2011

Oracle current session wait

oracle session wait

SET TIME ON VERIFY OFF
set linesize 200
COLUMN event FORMAT A30 HEADING 'Event' TRUNCATE
COLUMN wait_state FORMAT A7 HEADING 'State'
COLUMN seconds_in_wait FORMAT 999999999 HEADING 'Time|Waiting'
COLUMN wait_time FORMAT 999999 HEADING 'Time|Waited'
COLUMN p1 FORMAT A20 HEADING 'P1' TRUNCATE
COLUMN p2 FORMAT A20 HEADING 'P2' TRUNCATE
COLUMN p3 FORMAT A20 HEADING 'P3' TRUNCATE
col sid_serial format a15
col username format a20

SELECT vs.sid||','||vs.serial# sid_serial,
vs.username username,
vs.event,
CASE WHEN vs.state = 'WAITING' THEN vs.state
ELSE 'WAITED'
END wait_state,
vs.seconds_in_wait,
vs.wait_time,
DECODE(vs.p1text, NULL, NULL, vs.p1text||'='||vs.p1) p1,
DECODE(vs.p2text, NULL, NULL, vs.p2text||'='||vs.p2) p2,
DECODE(vs.p3text, NULL, NULL, vs.p3text||'='||vs.p3) p3
FROM v$session vs

No comments:

Oracle DBA

anuj blog Archive