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

[20251123]建立完善bcz.sql脚本.txt --//以前写过bind_cap.sql脚本。用于查询抓取的绑定变量值，查询那些变量使用的是like表达式。一些查询不是很方便，建立一个新的 --//脚本，命名bcz.sql用来支持正则表达式，更加灵活一些。 --//同时建立软连接bc.sql,bca.sql，这样不用打太多字符。 $ ln -s bind_cap.sql bc.sql $ ln -s bind_cap_awr.sql bca.sql --//代码如下： $ cat bcz.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. ---------------------------------------------------------------------------------------------- -- -- Name:        bcz.sql -- Purpose:     Display for binding variable types as well as for data information using regexp_like -- -- Author:      lfree -- Usage: --     @ bcz <sql_id> <column_name_gexp> -- ----------------------------------------------------------------------------------------------- set verify off 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 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')            ,'TIMESTAMP', TO_CHAR ( ANYDATA.accesstimestamp (value_anydata) ,'yyyy/mm/dd hh24:mi:ss.ff9')            ,value_string          )             value_string                 , inst_id     --        decode(datatype_string,'TIMESTAMP',ANYDATA.accesstimestamp (value_anydata)) c30     FROM gv$sql_bind_capture    WHERE     sql_id = '&1'          AND was_captured = 'YES'          AND DUP_POSITION IS NULL          AND regexp_like (lower(name) ,lower('&2')) --       ND LOWER (name) LIKE LOWER ('%' || NVL ('&&2', name) || '%') ORDER BY child_number, inst_id,was_captured, position; clear break --//简单测试如下： SCOTT@book01p> variable a1  number SCOTT@book01p> variable a2 varchar2(14) SCOTT@book01p> variable b3 varchar2(14) SCOTT@book01p> exec :a1 := 20; PL/SQL procedure successfully completed. SCOTT@book01p> exec :a2 := '20'; PL/SQL procedure successfully completed. SCOTT@book01p> exec :b3 := '20'; PL/SQL procedure successfully completed. SCOTT@book01p> select * from dept where deptno = :a1 and dname = :a2 and loc = :b3; no rows selected SCOTT@book01p> @ sql_id a5h28ggbxa25b -- SQL_ID = a5h28ggbxa25b come from shared pool select * from dept where deptno = :a1 and dname = :a2 and loc = :b3; SCOTT@book01p> @ bcz a5h28ggbxa25b '' SQL_ID        CHILD_NUMBER WAS NAME  POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING INST_ID ------------- ------------ --- ----- -------- ---------- ------------------- --------------- ------------ ------- a5h28ggbxa25b            0 YES :A1          1         22 2025-11-23 15:43:45 NUMBER          20                 1                            YES :A2          2         32 2025-11-23 15:43:45 VARCHAR2(32)    20                 1                            YES :B3          3         32 2025-11-23 15:43:45 VARCHAR2(32)    20                 1 SCOTT@book01p> @ bcz a5h28ggbxa25b a[1|2] SQL_ID        CHILD_NUMBER WAS NAME  POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING  INST_ID ------------- ------------ --- ---- --------- ---------- ------------------- --------------- ------------ -------- a5h28ggbxa25b            0 YES :A1          1         22 2025-11-23 15:43:45 NUMBER          20                  1                            YES :A2          2         32 2025-11-23 15:43:45 VARCHAR2(32)    20                  1 SCOTT@book01p> @ bcz a5h28ggbxa25b a SQL_ID        CHILD_NUMBER WAS NAME  POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING  INST_ID ------------- ------------ --- ---- --------- ---------- ------------------- --------------- ------------ -------- a5h28ggbxa25b            0 YES :A1          1         22 2025-11-23 15:43:45 NUMBER          20                  1                            YES :A2          2         32 2025-11-23 15:43:45 VARCHAR2(32)    20                  1 SCOTT@book01p> @ bcz a5h28ggbxa25b b|a SQL_ID        CHILD_NUMBER WAS NAME  POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING  INST_ID ------------- ------------ --- ---- --------- ---------- ------------------- --------------- ------------ -------- a5h28ggbxa25b            0 YES :A1          1         22 2025-11-23 15:43:45 NUMBER          20                  1                            YES :A2          2         32 2025-11-23 15:43:45 VARCHAR2(32)    20                  1                            YES :B3          3         32 2025-11-23 15:43:45 VARCHAR2(32)    20                  1