[20210112]完善查詢繫結變數指令碼bind_cap.txt

[20210112]完善查詢繫結變數指令碼bind_cap.txt

--//今天查詢一個語句繫結變數值,發現值查詢結果有點大,有時候並不需要檢視全部.
--//修改如下:

$ 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_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