Oracle 建立使用者與表空間完整指南

前言

在 Oracle 資料庫管理中，建立使用者並正確配置表空間是基礎且關鍵的操作。若未指定預設表空間，使用者將預設使用 SYSTEM 表空間，這會嚴重影響系統效能與穩定性。本文詳細說明建立使用者、授權、表空間管理的完整語法與實務範例。

一、建立使用者的標準流程

1. 先建立表空間

建立使用者前，必須確認資料庫中已存在所需的永久表空間與暫存表空間（如 USERS、TEMPTS01）。若不存在，請先以 CREATE TABLESPACE 指令建立。

2. 建立使用者範例

CREATE USER app_user IDENTIFIED BY password123
PROFILE DEFAULT
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMPTS01
ACCOUNT UNLOCK;

• DEFAULT TABLESPACE：指定物件儲存的預設永久表空間。
• TEMPORARY TABLESPACE：指定排序、雜湊等暫存操作使用的表空間。
• ACCOUNT UNLOCK：確保帳號為解鎖狀態。

二、使用者帳號基本管理指令

三、權限授予與回收

1. 系統權限

常用系統權限包括：

• CREATE SESSION：連線資料庫的基本權限，所有使用者皆需具備。
• CREATE TABLE、CREATE VIEW、CREATE PROCEDURE、CREATE SEQUENCE、CREATE SYNONYM 等物件建立權限。
• UNLIMITED TABLESPACE：允許在任意表空間無限量使用空間。
• DBA 級權限：SYSDBA、SYSOPER、ALTER SYSTEM、ALTER DATABASE 等。

授權語法： GRANT privilege TO username [WITH ADMIN OPTION];
回收語法： REVOKE privilege FROM username;

2. 物件權限

針對特定資料庫物件（表、視圖、序列等）授予操作權限。

語法： GRANT object_priv ON object_name TO username [WITH GRANT OPTION] [WITH HIERARCHY OPTION];
例如：GRANT SELECT, INSERT ON scott.emp TO app_user;

四、表空間完整管理指令

1. 建立表空間

CREATE TABLESPACE data01
DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 2G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

• AUTOEXTEND：啟用自動擴展，避免空間不足報錯。
• UNIFORM SIZE：指定區塊大小，提升分配效能。

2. 修改表空間狀態

3. 擴充表空間

• 新增資料檔： ALTER TABLESPACE ts_name ADD DATAFILE '/path/file.dbf' SIZE 1G;
• 調整檔案大小： ALTER DATABASE DATAFILE '/path/file.dbf' RESIZE 5G;
• 啟用自動擴展： ALTER DATABASE DATAFILE '/path/file.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

4. 刪除表空間

DROP TABLESPACE ts_name INCLUDING CONTENTS AND DATAFILES;

注意： 此操作會同時刪除表空間內所有物件與作業系統層級的資料檔，請謹慎執行。

5. UNDO 表空間管理

CREATE UNDO TABLESPACE undotbs02
DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 500M;

ALTER SYSTEM SET undo_tablespace = undotbs02;

同一時間僅能有一個 UNDO 表空間處於啟用狀態，切換需使用 ALTER SYSTEM SET。

6. 暫存表空間建立

CREATE TEMPORARY TABLESPACE temp_data
TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

五、表空間使用率監控查詢

SELECT 
    a.tablespace_name,
    ROUND(a.bytes/1024/1024,2) AS total_mb,
    ROUND(b.bytes/1024/1024,2) AS used_mb,
    ROUND(c.bytes/1024/1024,2) AS free_mb,
    ROUND(b.bytes*100/a.bytes,2) AS pct_used,
    ROUND(c.bytes*100/a.bytes,2) AS pct_free
FROM 
    (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a,
    (SELECT tablespace_name, SUM(bytes) bytes FROM dba_segments GROUP BY tablespace_name) b,
    (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) c
WHERE a.tablespace_name = b.tablespace_name
  AND a.tablespace_name = c.tablespace_name
ORDER BY pct_used DESC;

六、最佳實務建議

1. 永遠指定 DEFAULT TABLESPACE，避免物件誤建於 SYSTEM 表空間。
2. 分離永久、暫存、UNDO 表空間，減少 I/O 爭用。
3. 啟用 AUTOEXTEND 並設定 MAXSIZE，平衡自動化與空間管控。
4. 定期監控表空間使用率，設定閾值告警（如 85%）。
5. 授權遵循最小權限原則，僅授予業務必要的系統與物件權限。

本文整理自實務操作手冊，適用於 Oracle 10g 至 19c 版本。實際部署請依據企業規範與版本特性調整參數。