Oracle 跨環境資料遷移全攻略：權限授予、EXP/IMP、表空間重映射實戰

從生產/測試伺服器將 Oracle 資料遷移至本地/開發環境，涉及權限同步、資料導出導入、表空間重映射、存儲過程跨 Schema 呼叫等多環節。本文提供完整操作手冊：EXP/IMP 參數詳解、權限授予腳本、表空間建立語句、跨 Schema 存儲過程授權、Type 權限處理，並說明為何不建議攜帶原表空間定義導入。

階段一：跨 Schema 權限同步 (來源端)

讓目標使用者 (如 mid) 可存取來源使用者 (如 stat) 的物件：

-- 1. 以 stat 登入，產生授權腳本
SQL> SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON stat.' || table_name || ' TO mid;'
     FROM   user_tables;

-- 2. 以 SYSDBA 執行產生的授權語句
SQL> @grant_mid.sql

存儲過程跨 Schema 呼叫授權

-- stat 存儲過程需呼叫 mid 存儲過程
GRANT EXECUTE ANY PROCEDURE TO mid;
GRANT EXECUTE ANY PROCEDURE TO stat;

-- stat 存儲過程需存取 mid 的 TYPE (物件型別/集合)
GRANT EXECUTE ANY TYPE TO stat;

階段二：EXP 導出 (來源伺服器)

完整導出 (含所有使用者、表空間定義)

exp userid=system/manager@prod file=full_exp.dmp full=y

單一 Schema 導出 (推薦：不帶表空間定義，目標端自建)

exp userid=stat/stat@prod file=stat.dmp owner=stat
-- 參數說明：
-- owner=stat      : 僅導出 stat Schema
-- 無 tablespaces  : 不導出 CREATE TABLESPACE 語句
-- 無 grants       : 不導出權限 (目標端重新授予)
-- indexes=y       : 包含索引定義
-- constraints=y   : 包含約束定義

實際範例

exp djyy/djyy@zhwx file=c:\djyy20090921.dmp
exp wxzd/wxzd@zhwx file=c:\wxzd20090921.dmp

階段三：目標端準備 (本地資料庫)

1. 建立目標使用者與表空間

-- 建立表空間 (路徑、大小依環境調整)
CREATE TABLESPACE zhwxsys
  DATAFILE 'D:\Oracle\oradata\orcl\ZHWXSYS.dbf' SIZE 500M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

-- 建立臨時表空間
CREATE TEMPORARY TABLESPACE zhwxtmp
  TEMPFILE 'D:\Oracle\oradata\orcl\ZHWXTMP.dbf' SIZE 50M;

-- 建立使用者並指定預設/臨時表空間
CREATE USER stat IDENTIFIED BY stat
  DEFAULT TABLESPACE zhwxsys
  TEMPORARY TABLESPACE zhwxtmp;

GRANT CONNECT, RESOURCE TO stat;
-- 若需導入存儲過程/Type，可能需額外授權
GRANT CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TRIGGER TO stat;

2. TNS 連線配置 (本地 tnsnames.ora)

ZHWX =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

階段四：IMP 導入 (目標端)

方式一：PL/SQL Developer 圖形化導入

1. Tools → Import Tables → Oracle Import 標籤
2. Import Executable：指向 $ORACLE_HOME/bin/imp.exe (通常自動偵測)
3. Import File：選擇 .dmp 檔案
4. 執行 Import

方式二：命令列導入 (彈性最高)

-- 完整導入 (同一使用者名)
imp userid=stat/stat@zhwx file=c:\stat.dmp full=y ignore=y

-- 重映射 Schema (來源 stat → 目標 stat2)
imp userid=system/manager@zhwx file=c:\stat.dmp fromuser=stat touser=stat2

-- 僅導入特定表
imp userid=stat/stat@zhwx file=c:\stat.dmp tables=emp,dept

-- 關鍵參數：
-- ignore=y       : 表已存在時跳過建立錯誤，繼續導入資料
-- full=y         : 導入所有使用者 (需 SYSDBA)
-- fromuser/touser: Schema 重映射
-- remap_tablespace: 12c+ Data Pump 參數 (IMPDP)

Data Pump (EXPDP/IMPDP) 現代化替代方案 (10g+)

-- 導出 (需 Directory 物件)
expdp system/manager@prod directory=dp_dir dumpfile=stat.dmp schemas=stat

-- 導入 (支援重映射、並行、壓縮、加密)
impdp system/manager@local directory=dp_dir dumpfile=stat.dmp \
      remap_schema=stat:stat2 \
      remap_tablespace=old_ts:new_ts \
      table_exists_action=SKIP \
      parallel=4

常見問題與對策

最佳實務總結

1. 不攜帶表空間定義導出：目標端預先建立表空間，避免路徑衝突。
2. Schema 重映射：使用 fromuser/touser (EXP/IMP) 或 remap_schema (Data Pump)。
3. 權限分離：導出不含 Grants，目標端依最小權限原則重新授予。
4. 驗證清單：行數比對、約束狀態、索引有效性、存儲過程編譯狀態、同義詞完整性。
5. 優先 Data Pump：並行、壓縮、重映射、網路模式 (無中間檔案) 全方位優於舊 EXP/IMP。

跨環境遷移的核心在於「結構與資料分離、權限與業務分離、來源與目標環境解耦」。掌握 EXP/IMP 參數與 Data Pump 進階功能，即能從容應對各類遷移場景。