FULL SQL TEXT를 찾는 방법 (SQL FULLTEXT) Note 437304.1

OS환경 : Oracle Linux 6.8 (64bit)

 

DB 환경 : Oracle Database 11.2.0.4

 

방법 : FULL SQL TEXT를 찾는 방법 (SQL FULLTEXT) Note 437304.1

특정 process가 실행하고 있는 SQL을 확인하기 위해 전통적인 방법으로  v$sql, v$sql_text 등의 view를 사용함

이 뷰들은 datatype이 varchar2 이므로 full SQL을 볼 수 없는 제한이 있음

10g 부터는 SQL_FULLTEXT 라는 CLOB 형태의 컬럼을 통해 1000 byte 가 넘는 SQL text도 볼수 있게 되었음

 

1. test에 사용할 SQL text

2. 전통적 방식 사용(sql_text)

3. 새로운 방식 사용(sql_fulltext)

4. 요약

 

1. 테스트 쿼리는 영문만 사용하여 SELECT 부터 DUAL까지 1125 글자이므로, 1125 bytes 임

아래 쿼리를 imsi 계정으로 실행

SQL> select /* aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa */ * from dual;

 

 

2. 전통적 방식 사용(sql_text)

새로운 세션을 열어서 IMSI 유저세션의 sid와 serial#을 확인

SQL> select b.sid, b.serial#, b.username
from v$session b 
where b.username ='IMSI';
 
       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
    43    11691 IMSI

 

 

sid와 serial#로 SQL TEXT를 확인

VARCHAR2(1000)에 저장된 데이터이므로, 다음과 같이 FULL SQL은 확보하지 못함

SQL> 
set lines 200 pages 1000
col SQL for a200
select s.username username, s.osuser, s.program, a.sql_text SQL
from v$sql a, v$session s
where s.sid = 43 and s.serial# = 11691
and a.address = s.sql_address and a.hash_value = s.sql_hash_value;
 
USERNAME               OSUSER                  PROGRAM
------------------------------ ------------------------------ ------------------------------------------------
SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IMSI                   oracle                  sqlplus@ORACLE11 (TNS V1-V3)
select /* aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

 

 

3. 새로운 방식 사용(sql_fulltext)

새로운 세션을 열어서 IMSI 유저세션의 sid와 serial#을 확인

SQL> select b.sid, b.serial#, b.username
from v$session b 
where b.username ='IMSI';
 
       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
    43    11691 IMSI

 

 

sid와 serial#로 SQL TEXT를 확인

VARCHAR2(1000)에 저장된 데이터이지만,

CLOB datatype인 SQL_FULLTEXT 컬럼에 접근하였으므로 FULL SQL을 확보할수 있음

SQL> 
set lines 200 pages 1000
col sql_fulltext for a200
break on sql_fulltext skip 1 
select 
replace(translate(a.sql_fulltext,'0123456789','999999999'),'9','') SQL_FULLTEXT 
from v$sql a, v$session s
where s.sid = 43 and s.serial# = 11691
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','');
 
SQL_FULLTEXT
-------------------------------------------------------------------------------------------------------------
select /* aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa */ * from dual

 

 

4. 요약

10g 이상 버젼에서는, 다음과 같은 형태의 query를 응용함으로서 FULL SQL TEXT를 확보할 수 있음

SQL> 
set lines 200 pages 1000
col sql_fulltext for a200
select 
replace(translate(sql_fulltext,'0123456789','999999999'),'9','') SQL_FULLTEXT 
from v$sql 
where sql_text like '%<찾고자_하는_SQL키워드>%' 
group by replace(translate(sql_fulltext,'0123456789','999999999'),'9','') 
/ 

 

 

혹시라도 SQL TEXT가 4000 BYTE 이상 저장되어 있는 경우라면, ORA-22835 가 발생함

(VARCHAR2 형태로 출력되는 과정에서 최대 4000 BYTE 라는 제한조건이 발생됨)

이 경우, 다음과 같이 4000 byte만 읽도록 DBMS_LOB.SUBSTR()을 설정해줄 수 있음

*** 변경전
replace(translate(sql_fulltext,'0123456789','999999999'),'9','') 
 
*** 변경후
replace(translate(dbms_lob.substr(a.sql_fulltext,4000,1),'0123456789','999999999'),'9','')

만약 4000 BYTE 이상의 SQL text 가 모두 필요한 경우

java/proc/developer 등의 CLOB 처리가 가능한 tool를 사용해야함

 

 

참조 : https://community.oracle.com/thread/666392

437304.1