문서유형ㅣ기술정보
분야ㅣ모니터링/점검
적용제품버전ㅣ7FS02PS
문서번호ㅣTMOTI002
개요
바인드 변수 값을 확인할 수 있는 방법에 대해 설명합니다.
- view 이용
- ilog 이용
방법
view를 이용한 확인
BIND_VARIABLE_CAPTURE 파라미터가 YES로 되어 있어야 합니다.
V$SQL_BIND_CAPTURE_ALL에서 확인 가능합니다.
SQL> select * from V$SQL_BIND_CAPTURE_ALL; SQL_HASH_VALUE PLAN_HASH_VALUE SQL_ID CHILD_NUMBER USER_ID NAME POSITION TYPE -------------- --------------- ------------- ------------ ---------- -------------------- ---------- ---------- TYPE_NAME PRECISION SCALE LENGTH TIME_CAPTURED VALUE -------------------- ---------- ---------- ---------- --------------- -------------------- 3980673988 3845467455 9a1kqxzqn8hy4 124 18 ? 2 5 DATE 0 0 8 2025/03/12 2025/03/12 3980673988 3845467455 9a1kqxzqn8hy4 124 18 ? 1 3 VARCHAR2 0 0 32 2025/03/12 PARK 3980673988 3845467455 9a1kqxzqn8hy4 124 18 ? 0 1 NUMBER 38 -128 22 2025/03/12 1 3 rows selected.
#. SQL_TEXT와 함께 확인하는 구문 SQL> select a.SQL_ID, a.USER_ID, a.NAME, a.TYPE_NAME, a.VALUE, b.sql_text from V$SQL_BIND_CAPTURE_ALL a, v$sql b where a.SQL_ID = b.SQL_ID; #. 예시) SQL> select a.SQL_ID, a.USER_ID, a.NAME, a.TYPE_NAME, a.VALUE, b.sql_text from V$SQL_BIND_CAPTURE_ALL a, v$sql b where a.SQL_ID = b.SQL_ID; SQL_ID USER_ID NAME TYPE_NAME VALUE SQL_TEXT ------------- ---------- ---------- --------------- -------------------- -------------------------------------------------- 9a1kqxzqn8hy4 18 ? DATE 2025/03/12 SELECT * FROM test01 WHERE id = ? OR name = ? OR TO_CHAR(day, 'YYYY/MM/DD') = TO_CHAR(?,'YYYY/MM/DD') 9a1kqxzqn8hy4 18 ? VARCHAR2 PARK SELECT * FROM test01 WHERE id = ? OR name = ? OR TO_CHAR(day, 'YYYY/MM/DD') = TO_CHAR(?,'YYYY/MM/DD') 9a1kqxzqn8hy4 18 ? NUMBER 1 SELECT * FROM test01 WHERE id = ? OR name = ? OR TO_CHAR(day, 'YYYY/MM/DD') = TO_CHAR(?,'YYYY/MM/DD') 3 rows selected.
ilog를 이용한 확인
Tibero에 접속해서 아래의 TAG 리스트를 활성화합니다. TAG 활성화는 System 단위로 하며 활성화 Level은 Detail로 합니다.
SQL>ALTER SYSTEM ILOG ENABLE NAME 'BODY_MSG_CSR_BIND_PARAM_INFO' LEVEL DETAIL; SQL>ALTER SYSTEM ILOG ENABLE NAME 'CSR_USERPARAM_TO_STRNLOG_UNKNOWN' LEVEL DETAIL; SQL>ALTER SYSTEM ILOG ENABLE NAME 'BODY_SQL_PROCESS' LEVEL DETAIL; SQL>ALTER SYSTEM ILOG ENABLE NAME 'CSR_USERPARAM_TO_STRNLOG_NULL' LEVEL DETAIL; SQL>ALTER SYSTEM ILOG ENABLE NAME 'CSR_USERPARAM_TO_STRNLOG' LEVEL DETAIL;
$ cd /$TB_HOME/instance/$TB_SID/log/ilog $ ls -alt 합계 12844 -rw-r--r-- 1 jinhwa jinhwa 65536 3월 13 13:16 tibero7_2_2_287079-91.ilg -- 중략 -- $ tbiv tibero7_2_2_287079-91.ilg -- 중략 -- 03/13 13:16:40.5 0-0091 tbsvr_sq:277 SQL PROCESS: sql=SELECT * FROM test01 WHERE id = ? OR name = ? OR TO_CHAR(day, 'YYYY/MM/DD') = TO_CHAR(?, 'YYYY/MM/DD'), csr=2 03/13 13:16:40.5 0-0091 tbsvr_ms:955 BINDING[CSR_ID=2]: param #0, param_kind=IN, param_type=TB_TYPE_NUMBER 03/13 13:16:40.5 0-0091 csr.c:3761 BINDING[CSR_ID=2]: param #0: param_val_len=1, param_val: [1] 03/13 13:16:40.5 0-0091 tbsvr_ms:955 BINDING[CSR_ID=2]: param #1, param_kind=IN, param_type=TB_TYPE_VARCHAR 03/13 13:16:40.5 0-0091 csr.c:3761 BINDING[CSR_ID=2]: param #1: param_val_len=4, param_val: [PARK] 03/13 13:16:40.5 0-0091 tbsvr_ms:955 BINDING[CSR_ID=2]: param #2, param_kind=IN, param_type=TB_TYPE_DATE 03/13 13:16:40.5 0-0091 csr.c:3761 BINDING[CSR_ID=2]: param #2: param_val_len=10, param_val: [2025/03/13] Completed.