[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