Durham Oracle: July 2019

Very simple example to check sqlbind variable (will update as I go on - work in progress).

SQL > variable TABLSPC varchar2(50);

SQL> exec :TABLSPC := 'AP_MAIN';

PL/SQL procedure successfully completed.
SYS@abp1 SQL> select TABLESPACE_NAME, FILE_NAME from dba_data_files where tablespace_name = :TABLSPC

TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
AP_MAIN +DATA/AP/DATAFILE/ap_main.01.dbf

SQL>select sql_text, sql_id, LAST_ACTIVE_TIME from v$sqlarea where sql_text like '%TABLSPC%'

SQL_TEXT SQL_ID LAST_ACTI
------------------------------------------------------------------------------------------------------------------------ ------------- ---------
select FILE_NAME, TABLESPACE_NAME from dba_data_files where tablespace_name = :TABLSPC 6wg3unwbc0qk5 08-JUL-19

From here : http://www.oracle-wiki.net/startsqlshowbindvariable

SET PAUSE ON

SET PAUSE 'Press Return to Continue'

SET PAGESIZE 60

SET LINESIZE 300

COLUMN sql_text FORMAT A120

COLUMN sql_id FORMAT A13

COLUMN bind_name FORMAT A10

COLUMN bind_value FORMAT A26

SELECT sql_id, t.sql_text sql_text, b.name bind_name, b.value_string bind_value

FROM v$sql t JOIN v$sql_bind_capture b using (sql_id) WHERE

b.value_string is not null AND sql_id='&sqlid'

Enter value for sqlid: 6wg3unwbc0qk5
old 13: sql_id='&sqlid'
new 13: sql_id='6wg3unwbc0qk5'
Press Return to Continue
SQL_ID SQL_TEXT BIND_NAME BIND_VALUE
------------- ------------------------------------------------------------------------------------------------------------------------ ---------- --------------------------
6wg3unwbc0qk5 select FILE_NAME, TABLESPACE_NAME from dba_data_files where tablespace_name = :TABLSPC :TABLSPC AP_MAIN

Durham Oracle

Check sqlbind variable values

Oracle DBA silent response file