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

[20221208]完善bind_cap_awr.sql脚本.txt --//以前写的抓取绑定变量的脚本,如果绑定变量类型是timestamp类型是保存在v$sql_bind_capture.value_anydata中. --//我以前的写法如下,不合理多建立一个字段: $ cat bind_cap_awr.sql set verify off prompt prompt @bind_cap_awr sql_id [column] prompt column value_string format a50 column datatype_string format a15 break on snap_id on sql_id on was_captured on last_captured skip 1 select  replace(sql_fulltext,chr(13),'') c200 from v$sql where sql_id='&1' and rownum<=1; --select  replace(sql_text,chr(13),'') c200 from DBA_HIST_SQLTEXT where sql_id='&1' and rownum<=1; SELECT snap_id, INSTANCE_NUMBER,          sql_id,          was_captured,          last_captured,          name,          position,          max_length,          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 DBA_HIST_SQLBIND    WHERE sql_id = '&1' AND was_captured = 'YES' and  dup_position is null and lower(name) like lower('%'||nvl('&&2',name)||'%') ORDER BY snap_id ,last_captured, was_captured, position; clear break --//改写后代码如下: $ cat bind_cap_awr.sql set verify off prompt prompt @bind_cap_awr sql_id [column] prompt column value_string format a50 column datatype_string format a15 break on snap_id on sql_id on was_captured on last_captured skip 1 select  replace(sql_fulltext,chr(13),'') c200 from v$sql where sql_id='&1' and rownum<=1; --select  replace(sql_text,chr(13),'') c200 from DBA_HIST_SQLTEXT where sql_id='&1' and rownum<=1; SELECT snap_id         ,INSTANCE_NUMBER         ,sql_id         ,was_captured         ,last_captured         ,name         ,position         ,max_length         ,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 DBA_HIST_SQLBIND    WHERE     sql_id = '&1'          AND was_captured = 'YES'          AND dup_position IS NULL          AND LOWER (name) LIKE LOWER ('%' || NVL ('&&2', name) || '%') ORDER BY snap_id         ,last_captured         ,was_captured         ,position; clear break