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

[20191111]完善bind_cap.sql脚本.txt --//完善bind_cap.sql脚本,主要问题是绑定变量参数遇到timestamp类型.要从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 name=nvl('&&2',name)  order by child_number,was_captured,position; break on sql_id on child_number  skip 0 --//前面使用replace(sql_fulltext,chr(13),''),主要问题在于我们一些PB应用\r而没有使用\n换行，这样在sqlplus下 --//看到的很乱。 $ 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 a12 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,          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 name=nvl('&&2',name) ORDER BY snap_id ,last_captured, was_captured, position; break on snap_id on sql_id on was_captured on last_captured skip 0