[20221208]完善bind_cap.sql脚本.txt

[20221208]完善bind_cap.sql脚本.txt --//以前写的抓取绑定变量的脚本,如果绑定变量类型是timestamp类型是保存在v$sql_bind_capture.value_anydata中. --//我以前的写法如下,不合理多建立一个显示字段. $ cat bind_cap.sql set verify off column value_string format a50 column datatype_string format a15 break on sql_id on child_number  skip 1 select  replace(sql_fulltext,chr(13),'') c200 from v$sql where sql_id='&1' and rownum<=1; SELECT sql_id,        child_number,        was_captured,        name,        position,        max_length,        last_captured,        datatype_string,        DECODE (           datatype_string,           'DATE', TO_CHAR (TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss'),                            'yyyy/mm/dd hh24:mi:ss'),           value_string)           value_string,           decode(datatype_string,'TIMESTAMP',ANYDATA.accesstimestamp (value_anydata)) c30   FROM v$sql_bind_capture  WHERE sql_id = '&1' and was_captured='YES' and  DUP_POSITION is null and lower(name) like lower('%'||nvl('&&2',name)||'%')  order by child_number,was_captured,position; clear break --//改写后代码如下: $ cat bind_cap.sql set verify off column value_string format a50 column datatype_string format a15 break on sql_id on child_number  skip 1 select  replace(sql_fulltext,chr(13),'') c200 from v$sql where sql_id='&1' and rownum<=1; SELECT sql_id         ,child_number         ,was_captured         ,name         ,position         ,max_length         ,last_captured         ,datatype_string         ,DECODE          (             datatype_string            ,'DATE', TO_CHAR ( TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss') ,'yyyy/mm/dd hh24:mi:ss')            ,'TIMESTAMP', TO_CHAR ( ANYDATA.accesstimestamp (value_anydata) ,'yyyy/mm/dd hh24:mi:ss.ff9')            ,value_string          )             value_string     --        decode(datatype_string,'TIMESTAMP',ANYDATA.accesstimestamp (value_anydata)) c30     FROM v$sql_bind_capture    WHERE     sql_id = '&1'          AND was_captured = 'YES'          AND DUP_POSITION IS NULL          AND LOWER (name) LIKE LOWER ('%' || NVL ('&&2', name) || '%') ORDER BY child_number, was_captured, position; clear break