Oracle 表遷移四大方案對比：Exp/Imp、Move、CTAS、COPY 命令實戰

遷移場景分類

1. 同 Schema 不同表空間：調整儲存位置
2. 跨 Schema (用戶)：系統整合、架構調整、誤建修正

方案一：Exp/Imp / Expdp/Impdp (跨 Schema 首選)

傳統 Exp/Imp

# 導出
exp scott/pass@orcl file=emp.dmp log=exp.log tables=emp
# 導入 (用戶映射)
imp orcl/pass@orcl fromuser=scott touser=orcl file=emp.dmp log=imp.log tables=emp

Data Pump Expdp/Impdp (10g+ 推薦)

# 導出
expdp system/pass@orcl directory=dp_dir dumpfile=emp.dmp log=exp.log tables=scott.emp
# 導入 (Schema 重映射)
impdp system/pass@orcl directory=dp_dir dumpfile=emp.dmp log=imp.log tables=scott.emp remap_schema=scott:orcl

注意：外鍵約束隨導入建立，若參照表不存在會報錯

IMP-00017: ORA-00942: table or view does not exist
-- ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "DEPT" ("DEPTNO")

解決：先導入參照表、或導入時 CONSTRAINTS=N 稍後手動建約束。

方案二：ALTER TABLE MOVE (同 Schema 跨表空間)

ALTER TABLE emp MOVE TABLESPACE orcl;

• **索引變 UNUSABLE**，需重建
• 重建指定表空間：ALTER INDEX pk_emp REBUILD ONLINE TABLESPACE orcl;

方案三：CTAS (Create Table As Select) — 簡單快速

-- 權限授予
GRANT SELECT ON scott.emp TO orcl;
-- 切換用戶
CONN orcl/pass
-- 重建
CREATE TABLE emp AS SELECT * FROM scott.emp;

缺點

• **不複製預設值、約束、索引、權限**
• WHERE 條件不成立 → 僅建空表結構
• 適合臨時表、測試資料、小表

方案四：COPY 命令 (處理 LONG/LOB 型別)

-- 跨用戶複製
COPY FROM scott/pass@orcl TO orcl/pass@orcl REPLACE emp USING SELECT * FROM emp;

-- 同用戶換表空間 (先改默認表空間)
ALTER USER scott DEFAULT TABLESPACE orcl;
COPY FROM scott/pass@orcl TO scott/pass@orcl REPLACE emp USING SELECT * FROM emp;
-- REPLACE 會刪除舊表

• **不複製約束**，需手動補建
• LONG 型別無法用 MOVE/CTAS，COPY 是唯一選項

方案對比總結表

操作鐵律

1. **遷移前必備份、必測試**
2. 遷移期間**業務停止存取**該表
3. 完成後**核對行數、約束、索引、權限**
4. 大表建議分批、並行、監控 UNDO/TEMP 表空間