Rem Filename   : sidsql.sql
Rem 
Rem Parameters : &1 = sid.serial
Rem
Rem

SET LINESIZE 3500
SET PAGESIZE   0
SET VERIFY     OFF
SET FEEDBACK   OFF

ALTER SESSION set nls_numeric_characters = ',.';

@@header_dbinfo 107 DC

SET TERMOUT    OFF

SET PAGESIZE   9999
SET TERMOUT    ON
SET SERVEROUTPUT ON

CLEAR COLUMNS

DECLARE
    vsqlhashvalue number;
    type cc is ref cursor;
    tt cc;
BEGIN
     dbms_output.put_line('HOLA');
    for tt in
       (select sql_text
          from v$sqltext 
     where hash_value in (select sql_hash_value from v$session where sid = 123)
     order by piece) loop
     dbms_output.put_line(tt.sql_text);
     end loop;
     
     
    for tt in
(SELECT CHILD_NUMBER, LPAD(' ',6*DEPTH)||OPERATION|| '-' || OPTIONS OPERACION, 
       OBJECT_OWNER, OBJECT_NAME,OPTIMIZER,COST,CARDINALITY,BYTES,CPU_COST,
       IO_COST,TEMP_SPACE,ACCESS_PREDICATES,FILTER_PREDICATES
FROM V$SQL_PLAN
WHERE HASH_VALUE in (select sql_hash_value from v$session where sid = 123)
ORDER BY CHILD_NUMBER DESC) LOOP
     dbms_output.put_line(tt.OPERACION);
     end loop;
     
END;
/

UNDEFINE vsid = 0
UNDEFINE vserial = 0

SET FEEDBACK ON
CLEAR BREAKS
CLEAR COMPUTE