DB Wiki

SET LINES 2000 TRIMSPOOL ON PAGES 10000
COLUMN EVENT FORMAT A30
COLUMN secw FORMAT 99999
COLUMN username FORMAT A15
COLUMN sqlid_chno FORMAT A20

ACCEPT usr PROMPT 'Username (blank for all): '

SELECT  sw.SID, 
        s.username,
		sw.SEQ#,
		sw.EVENT,
		sw.WAIT_TIME,
		sw.SECONDS_IN_WAIT secw, 
		SQL_ID||' '||SQL_CHILD_NUMBER sqlid_chno,
		sw.STATE, 
		sw.P1,
		sw.P2
FROM v$session_wait sw,
     v$session s 
WHERE s.sid = sw.sid
AND sw.event != 'SQL*Net message from client'
AND s.username LIKE UPPER('%&&usr%');

set lines 10000 trimspool on pages 0 feed off verify off
set serverout on size 10000
SET LONG 10000
COLUMN sql_fulltext FORMAT A10000
ACCEPT vsql_id PROMPT 'Sql_id: '
ACCEPT vchild_number PROMPT 'child_number (0) : '

SPOOL /home/oracle/getq.txt
select sql_fulltext
FROM gv$sql
WHERE sql_id = '&&vsql_id'
and child_number = NVL('&&vchild_number',0);

DECLARE
l_bv_str   VARCHAR2(500) := NULL;
BEGIN
FOR l_bv_rec IN (SELECT bc.name, bc.value_string
                 FROM gv$SQL_BIND_CAPTURE bc
                 WHERE bc.sql_id = '&&vsql_id'
                 AND   bc.child_number = NVL('&&vchild_number',0)
                 ORDER BY bc.name) LOOP
    l_bv_str := l_bv_str||' '||l_bv_rec.name||'='||''''||l_bv_rec.value_string||'''';
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Bind values: '||l_bv_str);
END;
/

SPOOL OFF