[20240311]建立完善bind_cap.sql脚本.txt

[20240311]建立完善bind_cap.sql脚本.txt --//以前写的查询v$sql以及v$sql_bind_capture,换成gv$sql以及gv$sql_bind_capture,并且显示inst_id信息. $ cat bind_cap.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. ---------------------------------------------------------------------------------------------- -- -- Name:        bind_cap.sql -- Purpose:     Display for binding variable types as well as for data information -- -- Author:      lfree -- Usage: --     @ bind_cap <sql_id> <column_name> -- ----------------------------------------------------------------------------------------------- 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')            ,'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 LOWER (name) LIKE LOWER ('%' || NVL ('&&2', name) || '%') ORDER BY child_number, inst_id,was_captured, position; clear break