et termout off feedback off verify off linesize 200 define v_inst_number="&1" define v_sqlid="&2" set termout on serveroutput on size 1000000 format wrapped DECLARE v_low NUMBER; v_high NUMBER; v_end_t VARCHAR2(30); v_output VARCHAR2(200); type srec is record ( instance_number NUMBER, event VARCHAR2(64) , blocking_session NUMBER, waiting_list NUMBER); type stab is table of srec index by pls_integer; v_stab stab; CURSOR c1 (inst_number NUMBER, sqlid varchar2) IS SELECT inst_id, EVENT, nvl(BLOCKING_SESSION, 0), COUNT(SESSION_ID) waiting_list FROM user_ash WHERE SESSION_TYPE !='BACKGROUND' AND EVENT is NOT NULL AND INST_ID=inst_number and SQL_ID=sqlid --AND SNAP_ID BETWEEN begin_snap_id AND end_snap_id GROUP BY INST_ID, EVENT, BLOCKING_SESSION --HAVING COUNT(SESSION_ID) > 10 ORDER BY COUNT(SESSION_ID) DESC; BEGIN IF c1%isopen THEN CLOSE c1; END IF; OPEN c1(&v_inst_number,'&v_sqlid'); FETCH c1 bulk collect into v_stab; v_output:=''; v_output:=v_output||rpad('Instance',9,' '); v_output:=v_output||lpad('Blocking',74,' '); v_output:=v_output||lpad('Waiting Session',20,' '); DBMS_OUTPUT.PUT_LINE(v_output); v_output:=''; v_output:=v_output||rpad('Number',9,' '); v_output:=v_output||lpad('Event',64,' '); v_output:=v_output||lpad('Session',10,' '); v_output:=v_output||lpad('Count',10,' '); DBMS_OUTPUT.PUT_LINE(v_output); DBMS_OUTPUT.PUT_LINE(rpad('-',125,'-')); FOR j IN 1..v_stab.count LOOP v_output:=''; v_output:=v_output||rpad(to_char(v_stab(j).instance_number), 9,' '); v_output:=v_output||lpad(v_stab(j).event, 64,' '); IF v_stab(j).blocking_session = 0 THEN v_output:=v_output||lpad(' ', 10,' '); ELSE v_output:=v_output||lpad(to_char(v_stab(j).blocking_session), 10,' '); END IF; v_output:=v_output||lpad(to_char(v_stab(j).waiting_list), 10,' '); DBMS_OUTPUT.PUT_LINE(v_output); END LOOP; IF c1%isopen THEN CLOSE c1; END IF; END; /