[오라클] USER_SOURCE를 활용한 STORED PROCEDURE 내용 확인

녕하세요.

IT 업무 운영이나 프로젝트 진행 하시다 오라클 데이터베이스의 Function이나 Procedure등 소스 내용을 파악해야 할때가 있습니다. 그런데 Stored Procedure의 소스를 일일이 파악하기는 쉽지도 않을 뿐더러 비용대비 너무 비 효율입니다.

오라클에서는 Stored Procedure 소스를 'USER_SOURCE' Table로 관리를 하고 있으며 USER_SOURCE를 활용하시면

업무에 많은 도움이 되실 겁니다. 저도 업무에서 많이 활용하고 있는 Object 입니다.

 

 

 

오라클 USER_SOURCE 활용

USER_SOURCE 구조(Description)

SQL> DESC USER_SOURCE
   
        이름              유형
------------- ----------------
        NAME      VARCHAR2(30)
        TYPE      VARCHAR2(12)
        LINE            NUMBER
        TEXT    VARCHAR2(4000)

TYPE 종류

오라클 11g 기본 Sys 유저가 생성한 USER_SOURCE의 Type 종류 입니다.

 

SQL> SELECT DISTINCT(TYPE)
       FROM USER_SOURCE
   
   		TYPE
-------------------------
        FUNCTION
        JAVA SOURCE
        LIBRARY
        PACKAGE
        PACKAGE BODY
        PROCEDURE
        TRIGGER
        TYPE
        TYPE BODY

예제 - Procedure 리스트 확인

SQL>
    SELECT DISTINCT(NAME)
      FROM USER_SOURCE
     WHERE TYPE = 'PROCEDURE'

 

 

 

예제 - Procedure 소스 내용 확인

SQL>
   SELECT TEXT
    FROM USER_SOURCE
   WHERE TYPE = 'PROCEDURE'
     AND NAME = 'XMLVALIDATE'
     
     
결과>
    procedure xmlvalidate is
      p_num NUMBER;
    begin
      SELECT COUNT(*) INTO p_num
      FROM obj$
      WHERE type# = 29 AND owner# = 0 AND status <> 1
        AND ( 1=0
            OR name like 'javax/xml%'
            OR name like 'javax/xml/namespace%'
            OR name like 'javax/xml/parsers%'
            OR name like 'javax/xml/transform%'
            OR name like 'javax/xml/transform/dom%'
            OR name like 'javax/xml/transform/sax%'
            OR name like 'javax/xml/transform/stream%'
            OR name like 'oracle/xml/async%'
            OR name like 'oracle/xml/comp%'
            OR name like 'oracle/xml/jaxp%'
            OR name like 'oracle/xml/jdwp%'
            OR name like 'oracle/xml/mesg%'
            OR name like 'oracle/xml/parser%'
            OR name like 'oracle/xml/sql%'
            OR name like 'oracle/xml/util%'
            OR name like 'oracle/xml/xpath%'
            OR name like 'oracle/xml/xqxp%'
            OR name like 'oracle/xml/xslt%'
            OR name like 'org/w3c/dom%'
            OR name like 'org/xml/sax%'
            OR name like 'OracleXML%'
            OR name like 'oracle/xquery%'
        );
      IF p_num != 0 THEN
        dbms_registry.invalid('XML');
      ELSE
        dbms_registry.valid('XML');
      END IF;
      EXCEPTION WHEN no_data_found THEN
        dbms_registry.valid('XML');
    end xmlvalidate;

 

예제 - 소스에 'DEPT' 단어가 들어있는 Stored Procedure 찾기

SQL>
    SELECT *
      FROM USER_SOURCE
     WHERE TEXT LIKE '%DEPT%'
     
결과>
NAME			TYPE	LINE	TEXT
-------------------------------------------------------------
DBMS_DATA_MINING	PACKAGE	218	  tree_term_max_depth      CONSTANT VARCHAR2(30) := 'TREE_TERM_MAX_DEPTH';

DBMS_LOGMNR	PACKAGE	145	  --              DEPT:  NULL;

 

 

이상으로 USER_SOURCE Table을 활용한 Stroed Procedure 정보를 조회하는 방법에 대해

포스팅을 해드렸습니다.

 

업무에 도움이 되셨으면 합니다.