오라클 오브젝트 DDL 검색

오라클 DDL에 포함된 단어를 검색하는 방법

 

뷰, 함수, 패키지, 인덱스, 테이블 등 오라클의 모든 오브젝트의 DDL을 검색하는 방법이다.

 

샘플 테이블을 생성하고, DDL 스크립트를 저장한 뒤, 찾는 방법이다.

 

 

 

CREATE TABLE "스키마".OBJECT_DDL_SCRIPT
(
	OWNER VARCHAR2(100) NOT NULL
	, OBJECT_TYPE VARCHAR2(100) NOT NULL
	, OBJECT_NAME VARCHAR2(100) NOT NULL
	, DDL CLOB
	, CONSTRAINT "OBJECT_DDL_SCRIPT_PK" PRIMARY KEY (OWNER, OBJECT_TYPE, OBJECT_NAME)
)
;




INSERT INTO "스키마".OBJECT_DDL_SCRIPT
select 
	owner, object_type, object_name,
	dbms_metadata.get_ddl(object_type, object_name, owner) AS DDL
from
(
    --Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:
    select
        owner,
        --Java object names may need to be converted with DBMS_JAVA.LONGNAME.
        --That code is not included since many database don't have Java installed.
        object_name,
        decode(object_type,
            'DATABASE LINK',      'DB_LINK',
            'JOB',                'PROCOBJ',
            'RULE SET',           'PROCOBJ',
            'RULE',               'PROCOBJ',
            'EVALUATION CONTEXT', 'PROCOBJ',
            'CREDENTIAL',         'PROCOBJ',
            'CHAIN',              'PROCOBJ',
            'PROGRAM',            'PROCOBJ',
            'PACKAGE',            'PACKAGE_SPEC',
            'PACKAGE BODY',       'PACKAGE_BODY',
            'TYPE',               'TYPE_SPEC',
            'TYPE BODY',          'TYPE_BODY',
            'MATERIALIZED VIEW',  'MATERIALIZED_VIEW',
            'QUEUE',              'AQ_QUEUE',
            'JAVA CLASS',         'JAVA_CLASS',
            'JAVA TYPE',          'JAVA_TYPE',
            'JAVA SOURCE',        'JAVA_SOURCE',
            'JAVA RESOURCE',      'JAVA_RESOURCE',
            'XML SCHEMA',         'XMLSCHEMA',
            object_type
        ) object_type
    from dba_objects 
    where owner in ('스키마', '스키마2', '스키마3')
        --These objects are included with other object types.
        and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION', 'LOB','LOB PARTITION','TABLE PARTITION','TABLE SUBPARTITION')
        --Ignore system-generated types that support collection processing.
        and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')
        --Exclude nested tables, their DDL is part of their parent table.
        and (owner, object_name) not in (select owner, table_name from dba_nested_tables)
        --Exclude overflow segments, their DDL is part of their parent table.
        and (owner, object_name) not in (select owner, table_name from dba_tables where iot_type = 'IOT_OVERFLOW')
)
;


/* 
 * 조건 조회
 * */
SELECT * FROM "스키마".OBJECT_DDL_SCRIPT
WHERE 1=1
  AND INSTR(DDL, '검색어') > 0