Oracle SPOOL 導出 DDL 腳本實戰：單表、全 Schema、存儲過程與自訂 INSERT 語句

核心工具：DBMS_METADATA.GET_DDL + SPOOL

Oracle 內建 DBMS_METADATA 套件可取得物件完整建立語法，搭配 SPOOL 輸出至檔案，是遷移、備份、版控的利器。

環境設定（通用）

SET HEADING OFF
SET ECHO OFF
SET PAGESIZE 0
SET LONG 90000
SET FEEDBACK OFF
SET TRIMSPOOL ON

場景一：導出單一表與索引

SPOOL dept.sql
SELECT DBMS_METADATA.GET_DDL('TABLE', 'DEPT', 'SCOTT') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('INDEX', 'DEPT_IDX', 'SCOTT') FROM DUAL;
SPOOL OFF

場景二：導出整個 Schema 所有表與索引

SPOOL scott_schema.sql
CONNECT scott/tiger
SELECT DBMS_METADATA.GET_DDL('TABLE', u.table_name) FROM user_tables u;
SELECT DBMS_METADATA.GET_DDL('INDEX', u.index_name) FROM user_indexes u;
SPOOL OFF

場景三：導出指定 Schema 所有存儲過程

CONNECT brucelau/brucelau
SPOOL procedures.sql
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', u.object_name)
FROM user_objects u
WHERE object_type = 'PROCEDURE';
SPOOL OFF

進階：GET_DDL 參數說明

DBMS_METADATA.GET_DDL(
  object_type IN VARCHAR2,  -- 'TABLE', 'INDEX', 'PROCEDURE', 'VIEW', ...
  name        IN VARCHAR2,  -- 物件名稱
  schema      IN VARCHAR2 DEFAULT NULL  -- 擁有者，NULL 表當前用戶
) RETURN CLOB;

場景四：自訂導出 INSERT 語句（資料遷移）

透過字串串接動態產生 INSERT 語句：

SELECT 'INSERT INTO B_STATTEMPLATE (N_ID, C_NAME, C_KBH, N_PRINT, N_TYPE, N_APP, N_VALID) ' ||
       'VALUES (' || TO_CHAR(N_ID) || ', ''' || C_NAME || ''', ''' || C_KBH || ''', ' ||
       TO_CHAR(N_PRINT) || ', ' || TO_CHAR(N_TYPE) || ', 0, 0);'
FROM b_stattemplate;

將查詢結果 SPOOL 至檔案，即得資料遷移腳本。

結語

善用 DBMS_METADATA 與 SPOOL，可快速產出結構與資料遷移腳本，支援跨版本、跨平台部署，是 DBA 與開發人員的必備技能。