오라클 11G R2 이전 버전에서는 BUG으로 인해 v$SQL_BIND_CAPTURE에서 TIMESTAMP 형태의 bind 값이 NULL로 보입니다. 그러나 ANYDATA.AccessTimestamp(value_anydata) 를 이용해 볼수 있는 workaround 가 있군요.
참고: V$SQL_BIND_CAPTURE Does Not Show The Value For Binds Of Type TIMESTAMP (Doc ID 444551.1)
SQL> declare
bindts timestamp;
begin
bindts := systimestamp();
execute immediate 'select /* BIND_CAPTURE_TEST */ 1 from dual where :b1 is
not null' using bindts;
execute immediate 'select /* BIND_CAPTURE_TEST */ 1 from dual where :b1 is
not null' using bindts;
execute immediate 'select /* BIND_CAPTURE_TEST */ 1 from dual where :b1 is
not null' using bindts;
end;
/
PL/SQL procedure successfully completed.
SQL> select sql_id from v$sql where sql_fulltext like '%BIND_CAPTURE_TEST%'
and sql_fulltext not like '%xxx%' and command_type = 3;
SQL_ID
-------------
1mf1ch9vsr06a
SQL> select name, position, datatype_string, was_captured, value_string,
anydata.accesstimestamp(value_anydata) from v$sql_bind_capture where sql_id =
'1mf1ch9vsr06a';
NAME POSITION DATATYPE_STRING WAS
------------------------------ ---------- --------------- ---
VALUE_STRING
------------------------------------------------------------------------------
--
ANYDATA.ACCESSTIMESTAMP(VALUE_ANYDATA)
---------------------------------------------------------------------------
:B1 1 TIMESTAMP YES
05-JUL-07 12.20.23.311417000 PM
bindts timestamp;
begin
bindts := systimestamp();
execute immediate 'select /* BIND_CAPTURE_TEST */ 1 from dual where :b1 is
not null' using bindts;
execute immediate 'select /* BIND_CAPTURE_TEST */ 1 from dual where :b1 is
not null' using bindts;
execute immediate 'select /* BIND_CAPTURE_TEST */ 1 from dual where :b1 is
not null' using bindts;
end;
/
PL/SQL procedure successfully completed.
SQL> select sql_id from v$sql where sql_fulltext like '%BIND_CAPTURE_TEST%'
and sql_fulltext not like '%xxx%' and command_type = 3;
SQL_ID
-------------
1mf1ch9vsr06a
SQL> select name, position, datatype_string, was_captured, value_string,
anydata.accesstimestamp(value_anydata) from v$sql_bind_capture where sql_id =
'1mf1ch9vsr06a';
NAME POSITION DATATYPE_STRING WAS
------------------------------ ---------- --------------- ---
VALUE_STRING
------------------------------------------------------------------------------
--
ANYDATA.ACCESSTIMESTAMP(VALUE_ANYDATA)
---------------------------------------------------------------------------
:B1 1 TIMESTAMP YES
05-JUL-07 12.20.23.311417000 PM
'Oracle로 먹고살기' 카테고리의 다른 글
| ORACLE /usr/lib/pa20_64/dld.sl: Unable to find library 'libskgxn2.sl'. (0) | 2011/05/31 |
|---|---|
| ORACLE CRS Resource의 상태가 UNKNOW이 되는 일반적인 이유. (0) | 2011/04/22 |
| ORACLE v$sql_bind_capture에서 TIMESTAMP값이 안보이는 문제 workaround (0) | 2011/02/08 |
| Oracle client program의 모듈명, Action명 정의 방법 (0) | 2011/02/08 |
| ORACLE EM grid control log list (3) | 2011/01/06 |
| Oracle의 특정 process가 CPU를 많이 먹을때: kill 대신 suspend (0) | 2011/01/05 |
댓글을 달아 주세요