Oracle 匯入資料至指定表空間：預設表空間失效原因與 QUOTA/REVOKE/TVP 完整解決方案

現象：imp 導入忽略預設表空間，仍建立在原表空間

-- 導出用戶 EXP_USER / 表空間 EXP_TSPACE
-- 導入用戶 IMP_USER / 表空間 IMP_TSPACE
-- 已設定 IMP_USER DEFAULT TABLESPACE IMP_TSPACE
-- 但導入後表仍在 EXP_TSPACE

根因分析

1. 目標資料庫**存在同名表空間 EXP_TSPACE**
2. 導入用戶 IMP_USER **擁有 UNLIMITED TABLESPACE 系統權限** (含於 DBA/RESOURCE 角色)
3. Oracle 匯入邏輯：若目標表空間存在且用戶有權限，**優先使用原表空間名稱**，忽略 DEFAULT TABLESPACE

解決方案一：移除 UNLIMITED TABLESPACE + 配額授權 (標準做法)

-- 1. 撤銷無限表空間權限
REVOKE UNLIMITED TABLESPACE FROM IMP_USER;

-- 2. 僅授予目標表空間無限配額
ALTER USER IMP_USER QUOTA UNLIMITED ON IMP_TSPACE;

-- 3. (可選) 明確撤銷原表空間配額
ALTER USER IMP_USER QUOTA 0 ON EXP_TSPACE;

解決方案二：新建用戶最小權限模板 (最乾淨)

CREATE USER IMP_USER
IDENTIFIED BY "StrongP@ssw0rd"
DEFAULT TABLESPACE IMP_TSPACE
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
QUOTA UNLIMITED ON IMP_TSPACE;

-- 角色：僅基礎連線 + 匯入匯出
GRANT CREATE SESSION TO IMP_USER;
GRANT IMP_FULL_DATABASE TO IMP_USER;  -- 匯入需求
-- 不授 RESOURCE/DBA，避免 UNLIMITED TABLESPACE

解決方案三：修改 .dmp 檔案 (不推薦，風險高)

• 用 UltraEdit / 010 Editor 開啟 .dmp (二進位 + 明文混雜)
• 搜尋 TABLESPACE "EXP_TSPACE" → 批次替換為 TABLESPACE "IMP_TSPACE"
• 風險：檔案損壞、校驗和失效、二進位區段誤改、版本不相容

進階：Data Pump (expdp/impdp) 重映射表空間 (10g+ 推薦)

-- 導出
expdp system/pwd DIRECTORY=dp_dir DUMPFILE=exp.dmp SCHEMAS=EXP_USER

-- 導入並重映射
impdp system/pwd DIRECTORY=dp_dir DUMPFILE=exp.dmp \
    REMAP_TABLESPACE=EXP_TSPACE:IMP_TSPACE \
    REMAP_SCHEMA=EXP_USER:IMP_USER \
    TABLE_EXISTS_ACTION=SKIP

• REMAP_TABLESPACE 原生支援，無需調整權限/配額
• REMAP_SCHEMA 同步重映射用戶
• 效能優於傳統 exp/imp，支援並行、壓縮、網路模式

驗證腳本

SELECT owner, segment_name, tablespace_name
FROM dba_segments
WHERE owner = 'IMP_USER'
ORDER BY tablespace_name, segment_name;

總結選型