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

SET LINES 2000 TRIMSPOOL ON PAGES 10000
SET SERVEROUT ON SIZE 1000000

UNDEFINE sql_id
UNDEFINE child_id
ACCEPT sql_id PROMPT 'sql_id: '

SET VERIFY OFF FEED OFF

SELECT sql_id,child_number, TO_CHAR(last_active_time,'DDMonYY:HH24:MI:SS') last_active
FROM v$sql
WHERE sql_id = '&&sql_id'
ORDER BY last_active_time;

DEFINE child_id = '&&child_id'

spool /home/oracle/xp_&&sql_id._&&child_id..txt

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&&sql_id',NVL('&&child_id',0)));

DECLARE
l_bv_str   VARCHAR2(32767) := NULL;
BEGIN
FOR l_bv_rec IN (SELECT bc.name, bc.value_string
                 FROM v$SQL_BIND_CAPTURE bc
                 WHERE bc.sql_id = '&&sql_id'
                 AND   bc.child_number = '&&child_id'
                 ORDER BY bc.position) LOOP
    l_bv_str := l_bv_str||' '||l_bv_rec.name||'='||''''||l_bv_rec.value_string||'''';
END LOOP;
IF l_bv_str IS NULL THEN
   l_bv_str := 'n/a';
END IF;
DBMS_OUTPUT.PUT_LINE ('Bind values: '||l_bv_str);
END;
/

COLUMN rp        FORMAT a10
COLUMN cpu       FORMAT a10
COLUMN ela       FORMAT a10
COLUMN la        FORMAT a22
COLUMN uo        FORMAT a6
COLUMN fetches   FORMAT a8
COLUMN ex        FORMAT a8
COLUMN eofetches FORMAT a10
COLUMN ue        FORMAT a6
COLUMN loads     FORMAT a6

SELECT               TO_CHAR(s.rows_processed,'999999990') rp,
                     LTRIM(TO_CHAR(s.cpu_time/1000000,'999990.9')) cpu,
                     LTRIM(TO_CHAR(s.elapsed_time/1000000,'999999990.9')) ela,
                     LTRIM(TO_CHAR(s.last_active_time,'DD-MON-YYYY HH24:MI:SS')) la,
                     TO_CHAR(s.users_opening,'9990') uo,
                     TO_CHAR(s.fetches,'9999990') fetches,
                     TO_CHAR(s.executions,'9999990') ex,
                     TO_CHAR(s.end_of_fetch_count,'9999990') eofetches,
                     TO_CHAR(s.users_executing,'9990') ue,
                     TO_CHAR(s.loads,'9990') loads
from v$sql s
where s.sql_id = '&&sql_id'
and s.child_number = '&&child_id';

spool off
SET VERIFY ON FEED ON