Oracle 建立使用者與表空間標準流程

前言

Oracle 安裝完成後會建立預設資料庫，但實際應用通常需建立專屬使用者與表空間，隔離資料、控制配額、便於備份恢復。本文說明兩種常見建立模式：自訂表空間與使用預設表空間。

情境一：自訂表空間 (生產環境推薦)

1. 建立永久表空間

CREATE TABLESPACE app_data
DATAFILE '/u01/app/oracle/oradata/app_data01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 10G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO;

2. 建立暫存表空間

CREATE TEMPORARY TABLESPACE app_temp
TEMPFILE '/u01/app/oracle/oradata/app_temp01.dbf' SIZE 200M
AUTOEXTEND ON NEXT 50M MAXSIZE 5G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

3. 建立使用者並指定表空間

CREATE USER app_user IDENTIFIED BY "Str0ngP@ssw0rd"
DEFAULT TABLESPACE app_data
TEMPORARY TABLESPACE app_temp
PROFILE DEFAULT
ACCOUNT UNLOCK;

4. 授予核心權限

-- 基本連線與建表權限
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO app_user;

-- 表空間配額 (若不給 UNLIMITED TABLESPACE)
ALTER USER app_user QUOTA UNLIMITED ON app_data;

-- 進階權限 (視需求)
-- GRANT CREATE TRIGGER, CREATE TYPE, CREATE SYNONYM TO app_user;
-- GRANT SELECT ANY TABLE, INSERT ANY TABLE TO app_user;  -- 謹慎授予

情境二：使用預設表空間 (測試/開發環境快速建立)

-- 使用資料庫預設的 USERS (永久) 和 TEMP (暫存) 表空間
CREATE USER dev_user IDENTIFIED BY dev123
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
ACCOUNT UNLOCK;

GRANT CONNECT, RESOURCE TO dev_user;
-- CONNECT = CREATE SESSION
-- RESOURCE = CREATE TABLE, VIEW, SEQUENCE, PROCEDURE, TRIGGER, TYPE, SYNONYM 等
-- 若需 DBA 權限 (僅限開發/測試)
-- GRANT DBA TO dev_user;

關鍵物件對照表

常用後續管理指令

-- 修改使用者預設表空間
ALTER USER app_user DEFAULT TABLESPACE new_data;

-- 修改暫存表空間
ALTER USER app_user TEMPORARY TABLESPACE new_temp;

-- 解鎖/鎖定帳號
ALTER USER app_user ACCOUNT UNLOCK;
ALTER USER app_user ACCOUNT LOCK;

-- 修改密碼
ALTER USER app_user IDENTIFIED BY "NewP@ssw0rd";

-- 查看使用者配額
SELECT * FROM dba_ts_quotas WHERE username = 'APP_USER';

-- 刪除使用者 (含物件)
DROP USER app_user CASCADE;

最佳實務建議

1. 分離表空間：依業務模組/生命週期建立多個永久表空間 (如 core_data, log_data, archive_data)，便於備份、遷移、分層儲存。
2. 啟用 AUTOEXTEND + MAXSIZE：避免空間耗盡掛點，同時設定上限防止佔滿磁碟。
3. 使用 ASSM (AUTO SEGMENT SPACE MANAGEMENT)：SEGMENT SPACE MANAGEMENT AUTO 取代 freelists，減少爭用。
4. 最小權限原則：僅授予業務必要的系統權限，避免 GRANT DBA、ANY 權限濫用。
5. 密碼驗證函數：建立自訂 Profile 綁定 PASSWORD_VERIFY_FUNCTION 強制複雜度。
6. 稽核：關鍵使用者啟用 AUDIT SESSION, CREATE TABLE, DROP USER BY app_user。

提示：使用 DBCA (Database Configuration Assistant) 圖形化建立資料庫時，可在「儲存管理」步驟預先規劃表空間配置，避免事後手工調整。