超級 SQL 實戰：Oracle 物件導出導入與批量 DDL 生成技巧

場景

需將大量表的 CHAR 欄位轉為 VARCHAR2、去除尾部空格、批量重建索引/約束、導出存儲過程原始碼。核心思想：**用 SQL 生成 SQL，再批量執行**。

技巧 1：CHAR 轉 VARCHAR2 + TRIM 更新

SELECT 'ALTER TABLE ' || a.table_name || ' MODIFY ' || a.column_name || ' VARCHAR2(' || a.data_length || ');' AS modify_col,
       'UPDATE ' || a.table_name || ' SET ' || a.column_name || ' = TRIM(' || a.column_name || ');' AS update_data
FROM   user_tab_columns a, user_tables b
WHERE  a.table_name = b.table_name
AND    b.tablespace_name = 'YOUR_TBS_NAME'
AND    a.data_type = 'CHAR'
AND    a.table_name = 'TARGET_TABLE';

技巧 2：快速取得表欄位清單

SELECT table_name, column_name
FROM   user_tab_columns
WHERE  table_name = 'TABLENAME';

技巧 3：批量生成 DROP INDEX 語句

SELECT 'DROP INDEX "' || USER || '"."' || index_name || '";'
FROM   user_ind_columns
WHERE  table_name = 'TABLENAME';

輔助查詢：SELECT * FROM user_ind_columns;、SELECT * FROM user_indexes ORDER BY table_name;

技巧 4：查詢約束類型與關聯索引

SELECT constraint_name, constraint_type, index_name
FROM   user_constraints;

參考：Oracle9i Enhancement: Keep Index

技巧 5：導出用戶存儲過程/函數原始碼

SELECT text FROM all_source WHERE owner = USER;

技巧 6：批量生成 UPDATE 語句 (大量資料修正)

SELECT DISTINCT 'UPDATE tablename SET col1=1 WHERE col2=''' || col2 || ''';'
FROM   tablename;

執行生成的腳本即可完成百萬級資料修正，避免單條提交的網路往返開銷。

工具輔助

• SQL*Plus SPOOL 導出腳本檔，再 @script.sql 執行。
• SQL Developer / TOAD / PL/SQL Developer 均支援查詢結果直接匯出為 SQL 檔。
• 大批量操作建議分批次、離峰時段執行，並預先備份。

結語

「用 SQL 寫 SQL」是 DBA 處理大規模結構變更、資料修正的核心生產力工具。掌握資料字典視圖 (USER_*, ALL_*, DBA_*) 與字串拼接技巧，讓繁瑣手工作業變成可控、可重複、可審計的自動化流程。