從 Oracle 數據字典導出非唯一索引建立腳本，優化 EXP/IMP 效能

動機

EXP 預設導出索引，大型資料庫導入時重建索引極耗時。非唯一索引 (Non-unique) 可於導入後由腳本重建，導出時指定 INDEXES=N 可大幅縮短導入時間與備份檔體積。

數據字典關鍵視圖

• DBA_INDEXES：索引類型、表空間、儲存參數 (PCTFREE、INITIAL/NEXT/PCTINCREASE)。
• DBA_IND_COLUMNS：索引欄位、位置 (COLUMN_POSITION)、排序 (DESCEND ASC/DESC)。

五步驟產生腳本

步驟 1：建立視圖 — 非系統用戶非唯一索引欄位數

CREATE VIEW index_nouniq_column_num AS
SELECT t1.owner, t1.index_name, COUNT(0) AS column_num
FROM dba_indexes t1, dba_ind_columns t2
WHERE t1.uniqueness = 'NONUNIQUE'
  AND INSTR(t1.owner,'SYS') = 0
  AND t1.owner = t2.index_owner
  AND t1.index_name = t2.index_name
GROUP BY t1.owner, t1.index_name
ORDER BY t1.owner, column_num;

步驟 2：建立暫存表 — 彙整欄位清單

CREATE TABLE index_columns (
  index_owner VARCHAR2(30) NOT NULL,
  index_name  VARCHAR2(30) NOT NULL,
  column_names VARCHAR2(512) NOT NULL
) TABLESPACE users;

步驟 3：單欄位索引寫入暫存表

INSERT INTO index_columns
SELECT t1.owner, t1.index_name, t2.column_name
FROM index_nouniq_column_num t1, dba_ind_columns t2
WHERE t1.column_num = 1
  AND t1.owner = t2.index_owner
  AND t1.index_name = t2.index_name
ORDER BY t1.owner, t1.index_name;
COMMIT;

步驟 4：多欄位索引 — 使用函數/程序串聯欄位

CREATE OR REPLACE FUNCTION get_columns (
  p_owner VARCHAR2, p_name VARCHAR2, p_cnt NUMBER
) RETURN VARCHAR2 IS
  v_all VARCHAR2(512); v_i NUMBER := 0;
  CURSOR c1 IS SELECT column_name FROM dba_ind_columns
    WHERE index_owner = p_owner AND index_name = p_name ORDER BY column_position;
  v_rec c1%ROWTYPE;
BEGIN
  OPEN c1; FETCH c1 INTO v_rec;
  WHILE c1%FOUND LOOP
    v_i := v_i + 1;
    v_all := v_all || v_rec.column_name || CASE WHEN v_i = p_cnt THEN '' ELSE ',' END;
    FETCH c1 INTO v_rec;
  END LOOP; CLOSE c1; RETURN v_all;
EXCEPTION WHEN NO_DATA_FOUND THEN RETURN v_all; END;
/

CREATE OR REPLACE PROCEDURE populate_multi_columns IS
  v_cols VARCHAR2(2000);
  CURSOR c1 IS SELECT * FROM index_nouniq_column_num WHERE column_num >= 2;
  v_rec c1%ROWTYPE;
BEGIN
  OPEN c1; FETCH c1 INTO v_rec;
  WHILE c1%FOUND LOOP
    v_cols := get_columns(v_rec.owner, v_rec.index_name, v_rec.column_num);
    INSERT INTO index_columns VALUES (v_rec.owner, v_rec.index_name, v_cols);
    FETCH c1 INTO v_rec;
  END LOOP; COMMIT; CLOSE c1;
EXCEPTION WHEN OTHERS THEN ROLLBACK; END;
/
EXEC populate_multi_columns;

步驟 5：產生最終建索引腳本 (create_index.sql)

SET HEADING OFF PAGESIZE 5000
TRUNCATE TABLE index_columns;
EXEC populate_multi_columns;
-- 重跑單欄位 (冪等)
INSERT INTO index_columns ... (同上步驟3) ...; COMMIT;

SPOOL /Oracle_backup/log/create_index.sql
SELECT 'CREATE INDEX '||t1.owner||'.'||t1.index_name||CHR(10)||
       ' ON '||t1.table_name||' ('||t2.column_names||')'||CHR(10)||
       ' TABLESPACE '||t1.tablespace_name||CHR(10)||
       ' PCTFREE '||t1.pct_free||CHR(10)||
       ' STORAGE (INITIAL '||t1.initial_extent||
       ' NEXT '||t1.next_extent||
       ' PCTINCREASE '||t1.pct_increase||');'||CHR(10)||CHR(10)
FROM dba_indexes t1, index_columns t2
WHERE t1.owner = t2.index_owner AND t1.index_name = t2.index_name
ORDER BY t1.owner, t1.table_name;
SPOOL OFF

操作流程

1. 源庫執行上述步驟 1-5，產生 create_index.sql。
2. 目標庫 IMP INDEXES=N 導入資料 (不建索引)。
3. 目標庫執行 @/Oracle_backup/log/create_index.sql 並行/串列重建非唯一索引。

注意事項

• 僅處理 ASC 排序；DESC 索引較少見，需另行擴展。
• 唯一索引/主鍵/外鍵支撐索引 不可省略，必須隨 IMP INDEXES=Y 導入或預先建立。
• 儲存參數 (INITIAL/NEXT/PCTINCREASE) 來自 DBA_INDEXES，若目標表空間管理方式不同 (LMT vs DMT) 可能需手動調整。