[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