SQLPlus를 사용하여 오라클 전체 Schema 추출

set pagesize 0
set long 90000
set termout off
set feedback off
set echo off

— schema.sql 파일로 DUMP
spool schema.sql

— 임시 테이블 (BIN$…) 과 “PACKAGE BODY”를 제외한 전체 객체 가져오기
SELECT
  dbms_metadata.GET_DDL(u.object_type,u.object_name)
FROM
  user_objects u
WHERE
      object_id IS NOT NULL
  AND object_type != ‘PACKAGE BODY’
  AND object_name NOT LIKE ‘BIN$%==$0′
ORDER BY
  object_type ASC;

— “PACKAGE BODY’ 객체 가져오기
SELECT
  dbms_metadata.GET_DDL(‘PACKAGE’,u.object_name)
FROM
  user_objects u
WHERE
      object_id IS NOT NULL
  AND object_type = ‘PACKAGE BODY’;

spool off