[ORACLE] LITERAL SQL 검출 쿼리

with literal as (select FORCE_MATCHING_SIGNATURE,
               count(*) cnt
          from v$sql
         where FORCE_MATCHING_SIGNATURE <> 0
         group by FORCE_MATCHING_SIGNATURE
        having count(*) > 10
         order by 2 desc)
select text,
       cnt,
       sql_id,
       (select name
          from AUDIT_ACTIONS v
         where v.action = COMMAND_TYPE) command_Type,
       FORCE_MATCHING_SIGNATURE
  from (
select text,
               count(*) as cnt,
               sql_id,
               0 FORCE_MATCHING_SIGNATURE,
               COMMAND_TYPE
          from (select to_char(substr(SQL_fullTEXT, 1, 100)) text ,sql_id,--텍스트 길이 조절
                       COMMAND_TYPE
                  from v$sql
                 where FORCE_MATCHING_SIGNATURE = '0'
                   and PARSING_SCHEMA_NAME = 'DSTEMUSR' --원하시는 쿼리 수행 계정 입력
                   and COMMAND_TYPE = 2 )
         group by text,sql_id,
               COMMAND_TYPE
        having count(*) > 10
         union all
select min(to_char(substr(SQL_fullTEXT, 1, 100))) text,
               a.cnt,
               b.sql_id,
               a.FORCE_MATCHING_SIGNATURE,
               b.command_Type
          from literal a,
               v$sql b
         where a.FORCE_MATCHING_SIGNATURE = b.FORCE_MATCHING_SIGNATURE
           and B.PARSING_SCHEMA_NAME = 'DSTEMUSR'  --원하시는 쿼리 수행 계정 입력
         group by a.FORCE_MATCHING_SIGNATURE,b.sql_id,
               a.cnt,
               b.command_Type )
 order by command_Type,
       cnt desc ;