[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