I modified the bind variable extraction script that I normally use to make it more helpful to me.
Here was my earlier post with the old script: blog post
Here is my updated script:
set termout on set echo on set linesize 32000 set pagesize 1000 set trimspool on column NAME format a3 column VALUE_STRING format a17 spool bind2.log select * from (select distinct to_char(sb.LAST_CAPTURED,'YYYY-MM-DD HH24:MI:SS') DATE_TIME, sb.NAME, sb.VALUE_STRING from DBA_HIST_SQLBIND sb where sb.sql_id='gxk0cj3qxug85' and sb.WAS_CAPTURED='YES') order by DATE_TIME, NAME; spool off
Replace gxk0cj3qxug85 with the sql_id of your own query.
The output looks like this (I’ve scrambled the values to obscure production data):
DATE_TIME NAM VALUE_STRING ------------------- --- ----------------- 2014-08-29 11:22:13 :B1 ABC 2014-08-29 11:22:13 :B2 DDDDDD 2014-08-29 11:22:13 :B3 2323 2014-08-29 11:22:13 :B4 555 2014-08-29 11:22:13 :B5 13412341 2014-08-29 11:22:13 :B6 44444 2014-08-29 11:26:47 :B1 gtgadsf 2014-08-29 11:26:47 :B2 adfaf 2014-08-29 11:26:47 :B3 4444 2014-08-29 11:26:47 :B4 5435665 2014-08-29 11:26:47 :B5 4444 2014-08-29 11:26:47 :B6 787
This is better than the original script because it keeps related bind variable values together.
– Bobby
4/27/21
Current version bind2.sql
I have similar script, but it also shows additional information from v$sql_bind_capture and real-time sql monitor(if version >=11) and with avoiding bug #6156624:
https://github.com/xtender/xt_scripts/blob/master/binds.sql
Thanks for your comment and the link to your script.