full sql text
FULL SQL TEXT를 찾는 방법
게시일: Jun 3, 2008 8:05 PM
Click to report abuse... 이 글타래에 댓글을 올리려면 클릭하십시오 댓글
FULL SQL TEXT를 찾는 방법
PURPOSE
전통적으로 특정 process가 실행하고 있는 SQL을 살펴보기 위해 V$SQL / V$SQL_TEXT 등의 view가 사용됩니다. 다만, varchar2 datatype이므로 full SQL을 볼 수 없다는 제한이 있습니다.
10g 부터는 SQL_FULLTEXT 라는 CLOB 형태의 컬럼을 통해 1000 byte 가 넘는 SQL text도 열람할 수 있게 되었습니다.
CONTENTS
1. test에 사용할 SQL text
2. 전통적 방법으로 접근
3. 새로운 방법으로 접근
4. 요약
1. 다음과 같은 TEST 목적의 query가 있습니다. 영문캐릭터만 사용하여 SELECT 부터 DUAL까지 1125 글자이므로, 1125 bytes 입니다.
select /*
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
*/ * from dual;
이 query를 SCOTT 으로 수행해봅니다.
2. 전통적 방법으로 접근
새로운 SESSION을 열어 다음과 같이, SCOTT 으로 접속된 SESSION의 sid 및 serial# 를 확인합니다.
(1) USERNAME을 통해 sid 및 serial# 를 확인
select b.sid, b.serial#, b.username
from v$session b where b.username ='SCOTT';
SID SERIAL# USERNAME
---------- 309 1198 SCOTT
(2) 확인된 정보를 통해, SQL TEXT를 확인합니다.
VARCHAR2(1000)에 저장된 DATA 이므로, 다음과 같이 FULL SQL은 확보하지 못했습니다.
set linesize 50
column sql_fulltext format a60 word_wrap
break on sql_text skip 1
select s.username username, s.osuser, s.program, a.sql_text SQL
from v$sql a, v$session s
where s.sid = 309 and s.serial# = 1198
and a.address = s.sql_address and a.hash_value = s.sql_hash_value;
USERNAME
OSUSER
PROGRAM
SQL
SCOTT
oracle
sqlplus@prdsup2.kr.oracle.com (TNS V1-V3)
select /* aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
USERNAME
OSUSER
PROGRAM
SQL
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
USERNAME
OSUSER
PROGRAM
SQL
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
USERNAME
OSUSER
PROGRAM
SQL
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
USERNAME
OSUSER
PROGRAM
SQL
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
3. 새로운 방법으로 접근
새로운 SESSION을 열어 다음과 같이, SCOTT 으로 접속된 SESSION의 sid 및 serial# 를 확인합니다.
(1) USERNAME을 통해 sid 및 serial# 를 확인
select s.username username, s.osuser, s.program, a.sql_text SQL
from v$sql a, v$session s
where s.sid = 309 and s.serial# = 1198
and a.address = s.sql_address and a.hash_value = s.sql_hash_value;
SID SERIAL# USERNAME
---------- 309 1198 SCOTT
(2) 확인된 정보를 통해, SQL TEXT를 확인합니다.
VARCHAR2(1000)에 저장된 DATA 이므로, CLOB datatype인 SQL_FULLTEXT 컬럼에 접근하였으므로 full SQL text를 확보할 수 있습니다.
set linesize 50
column sql_fulltext format a60 word_wrap
break on sql_text skip 1
select
replace(translate(a.sql_fulltext,'0123456789','999999999'),'9','')
SQL_FULLTEXT
from v$sql a, v$session s
where
s.sid = 309 and s.serial# = 1198
and a.address = s.sql_address and a.hash_value = s.sql_hash_value
and a.sql_text like '%aaaaaaaaaaa%'
group by replace(translate(a.sql_fulltext,'0123456789','999999999'),'9','');
set linesize 50
column sql_fulltext format a60 word_wrap
break on sql_text skip 1
SQL_FULLTEXT
select /*
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
SQL_FULLTEXT
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
SQL_FULLTEXT
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
*/ * from dual
4. 요약
10g 이상 버젼에서는, 다음과 같은 형태의 query를 응용함으로서 FULL SQL TEXT를 확보할 수 있습니다.
select
replace(translate(sql_fulltext,'0123456789','999999999'),'9','')
SQL_FULLTEXT
from v$sql
where sql_text like '%<찾고자_하는_SQL_KEYWORD>%'
group by replace(translate(sql_fulltext,'0123456789','999999999'),'9','')
/
NOTE
혹시라도 SQL TEXT가 4000 BYTE 이상 저장되어 있는 경우라면, ORA-22835 가 발생합니다. (VARCHAR2
형태로 출력되는 과정에서 최대 4000 BYTE 라는 제한조건이 발생됩니다) 이 경우, 다음과 같이 4000 byte만 읽도록
DBMS_LOB.SUBSTR()을 설정해줄 수 있습니다.
*** BEFORE
replace(translate(sql_fulltext,'0123456789','999999999'),'9','')
*** AFTER
replace(translate(dbms_lob.substr(a.sql_fulltext,4000,1),'0123456789','999999999'),'9','')
만약 4000 BYTE 이상의 SQL text 가 모두 필요, java/proc/developer 등의 CLOB 처리가 가능한 3rd part tool를 사용해주시거나, 필요한 APP를 작성해주셔야 합니다.
References