Pull bind values from v$SQL_MONITOR - including timestamp

 

Make sure you have the diagnostics pack before using this. 

Only works on 18c onwards. see https://ora-00932.blogspot.com/2022/07/column-lowhigh-values-adding-timestamp.html for more details 

Formats bind values ready to be run in SQL*PLUS for testing. 

select  sql_id 
    , to_char( sql_exec_start , 'DD-MON HH24:MI:SS' ) exec_start 
    , to_char( sql_exec_start + elapsed_time/1000000 /60/60/24 , 'HH24:MI:SS' ) exec_end 
    , round(elapsed_time/1000000 , 1) "Elap(s)" 
    , status "Exec Status" 
    , SQL_PLAN_HASH_VALUE "Plan Hash" 
    , listagg ( 
          '    '||bind_name ||' '|| dtystr ||' := ' 
          || case
               when dtystr like 'VARCHAR%'   then ''''||bind_value ||'''' 
               when dtystr like 'DATE'       then ' to_date( '''||bind_value ||''' , ''YYYY/MM/DD HH24:MI:SS'') ' 
               when dtystr like 'NUMBER'     then bind_value  
               when dtystr like 'TIMESTAMP'  then  ' to_timestamp( '''||
                          to_char(dbms_stats.convert_raw_to_date(hextoraw(bind_value )), 'YYYY/MM/DD HH24:MI:SS')
                         ||''' , ''YYYY/MM/DD HH24:MI:SS'') ' 
            end  ||'; '
     , chr(10) ) within group (order by pos )||chr(10)   as bind_vals 
     , ( select sql_fulltext from v$sqlarea a where a.sql_id = m.sql_id ) sql_fulltext 
from (
    select * 
     from v$sql_monitor  
     where  binds_xml is not null
     and sql_id = 'xxxxxxxxxx'  
) m
, xmltable ( '/binds/bind' passing 
          XMLTYPE(  m.binds_xml ) 
        columns
           bind_name varchar2(128) path '@name' 
          ,pos number path '@pos' 
          ,dty number path '@dty' 
          ,dtystr varchar2(128)  path '@dtystr' 
          ,maxlen number path '@maxlen' 
          ,len number path '@len' 
          ,bind_value varchar2(128) path 'text()' 
)   b 
group by sql_id ,  sql_exec_start, elapsed_time , status , SQL_PLAN_HASH_VALUE
order by  exec_start;

12c version (timestamp is truncated to seconds) 

select  sql_id 
    , to_char( sql_exec_start , 'DD-MON HH24:MI:SS' ) exec_start 
    , to_char( sql_exec_start + elapsed_time/1000000 /60/60/24 , 'HH24:MI:SS' ) exec_end 
    , round(elapsed_time/1000000 , 1) "Elap(s)" 
    , status "Exec Status" 
    , SQL_PLAN_HASH_VALUE "Plan Hash" 
    , listagg ( 
          '    '||bind_name ||' '|| dtystr ||' := ' 
          || case
               when dtystr like 'VARCHAR%'   then ''''||bind_value ||''''   
               when dtystr = 'DATE' or dtystr like 'TIMESTAMP%'  then 'to_'||dtystr||' ( ''' ||to_char( to_date( 
               
                        ( select listagg(to_char(
                              to_number(  regexp_substr( dump( hextoraw( bind_value )) , '[0-9]+', 1, level+2 )  , '999')
                              - case when level in(1,2) then 100 when level  in(5,6,7) then 1 else 0 end
                           , 'FM00')) within group( order by level  )
                          from dual connect by rownum <= 7 
                        ) , 'YYYYMMDDHH24MISS'), 'DD-Mon-YYYY HH24:MI:SS' )
                                         || ''', ''DD-Mon-YYYY HH24:MI:SS'')' 
               when dtystr like 'NUMBER'     then bind_value  
            end  ||'; '
     , chr(10) ) within group (order by pos ) ||chr(10)   as bind_vals 
     , ( select sql_fulltext from v$sqlarea a where a.sql_id = m.sql_id ) sql_fulltext 
from (
    select * 
     from v$sql_monitor  
     where  binds_xml is not null
     and sql_id = 'xxxxxxxxxx'  
) m
, xmltable ( '/binds/bind' passing 
          XMLTYPE(  m.binds_xml ) 
        columns
           bind_name varchar2(128) path '@name' 
          ,pos number path '@pos' 
          ,dty number path '@dty' 
          ,dtystr varchar2(128)  path '@dtystr' 
          ,maxlen number path '@maxlen' 
          ,len number path '@len' 
          ,bind_value varchar2(128) path 'text()' 
)   b 
group by sql_id ,  sql_exec_start, elapsed_time , status , SQL_PLAN_HASH_VALUE
order by  exec_start;