Oracle 11g/12c/19c 新建資料庫、遠程用戶、表空間授權全流程實戰

從裸機到可業務訪問，涵蓋 DBCA 建庫、表空間規劃、用戶創建、權限模型、遠程連接配置、Schema 遷移六大環節。適合初學者建立標準化交付流程。

第一階段：建立資料庫 (DBCA 圖形化)

1. 啟動 Database Configuration Assistant
2. 選「Create Database」→ 模板選「General Purpose」或「Custom」
3. 輸入 Global Database Name (如 db2.example.com) 與 SID (如 db2)
4. 字符集建議 AL32UTF8 (Unicode 全支持)
5. 存儲路徑建議：E:\app\admin\oradata\db2\ (分離軟體與數據)
6. 完成向導，可保存生成腳本備份

第二階段：表空間規劃與創建

-- 業務表空間 (自動擴展)
CREATE TABLESPACE db2tabs
DATAFILE 'E:\app\admin\oradata\db2\db2tabs.dbf'
SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

-- 索引表空間 (分離 IO)
CREATE TABLESPACE db2idx
DATAFILE 'E:\app\admin\oradata\db2\db2idx.dbf'
SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

第三階段：用戶創建與表空間綁定

CREATE USER user2 IDENTIFIED BY user2
DEFAULT TABLESPACE db2tabs
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON db2tabs
QUOTA 100M ON db2idx;

-- 常用帳號維護
ALTER USER user2 IDENTIFIED BY newpass;          -- 改密
ALTER USER user2 DEFAULT TABLESPACE new_tbs;     -- 換表空間
ALTER USER user2 ACCOUNT LOCK/UNLOCK;            -- 鎖定/解鎖

第四階段：權限模型 (角色化最佳實踐)

-- 1. 創建業務角色
CREATE ROLE app_role;

-- 2. 授予核心權限
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE,
      CREATE PROCEDURE, CREATE TRIGGER, CREATE SYNONYM
TO app_role;

-- 3. 表空間配額 (可在角色層面統一)
GRANT UNLIMITED TABLESPACE TO app_role;  -- 或按表空間配額

-- 4. 用戶歸屬角色
GRANT app_role TO user2;

-- 5. 對象級授權 (跨 Schema)
GRANT SELECT ON scott.dept TO app_role WITH GRANT OPTION;

第五階段：遠程連接配置 (客戶端 TNSNAMES)

# tnsnames.ora
DB2_REMOTE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db2.example.com)
    )
  )

驗證：TNSPING DB2_REMOTE → SQLPLUS user2/user2@DB2_REMOTE

第六階段：Schema 級遷移 (EXP/IMP 或 Data Pump)

1. 源端導出：EXPDP scott/tiger@db1 DIRECTORY=dpump DUMPFILE=scott.dmp SCHEMAS=scott
2. 目標端建用戶+表空間+角色授權 (同上)
3. 導入：IMPDP user2/user2@db2 DIRECTORY=dpump DUMPFILE=scott.dmp REMAP_SCHEMA=scott:user2 REMAP_TABLESPACE=users:db2tabs

關鍵參數：REMAP_SCHEMA 重映射擁有者，REMAP_TABLESPACE 重映射表空間。

常見坑點提醒

• Global Database Name ≠ SID：前者含域名，用於網路標識；後者實例標識
• 字符集選錯不可逆：務必用 AL32UTF8
• 默認表空間 USERS 勿存業務數據：規劃專用表空間
• 遠程連接失敗 90% 是 TNSNAMES/LISTENER 不匹配或防火牆
• Data Pump 需 Directory Object 權限：GRANT READ,WRITE ON DIRECTORY dpump TO user2