DB Wiki

Get Query

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