오라클 메타정보 추출

1. 기본 환경

  set heading off;

  set echo off;

  set long 64000  longchunksize 64000  pagesize 0   linesize 1000  feedback off verify off  trimspool on

  exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true); 

2. 테이블

   SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT') FROM DUAL;

3. 테이블

   SELECT DBMS_METADATA.GET_DDL('INDEX','PK_EMP','SCOTT') FROM DUAL;

4. 패키지

   SELECT DBMS_METADATA.GET_DDL('PACKAGE','EMP_PKG','SCOTT') FROM DUAL;

5. 패키지 바디

  SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','EMP_PKG','SCOTT') FROM DUAL;

6. 테이블스페이스

    SELECT DBMS_METADATA.GET_DDL('TABLESPACE','USERS') FROM dual;

7. foreign key 

    SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','<table_name>','<schema>') from dual;

8. schema grant

    SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<schema>') from dual;

  SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<schema>') from dual;

  SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','<schema>') from dual;