[20251123]建立完善bcaz.sql脚本.txt

[20251123]建立完善bcaz.sql脚本.txt $ cat bcaz.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. ---------------------------------------------------------------------------------------------- -- -- Name:        bcaz.sql.sql -- Purpose:     Display for binding variable types as well as for data information using regexp_like -- -- Author:      lfree -- Usage: --     @ bcaz <sql_id> <column_name_regexp> -- ----------------------------------------------------------------------------------------------- set verify off prompt prompt @bind_cap_awr sql_id [column] prompt column value_string format a50 column datatype_string format a15 set term off col 2 new_value 2 select null "2" from dual where 1=2; select decode('&2',null,'*','&2') "2" from dual; set term on break on snap_id on sql_id on was_captured on last_captured skip 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 regexp_like (lower(name) ,lower('&2'))          --AND LOWER (name) LIKE LOWER ('%' || NVL ('&&2', name) || '%') ORDER BY snap_id         ,last_captured         ,was_captured         ,position; clear break