Oracle 10g 表空間建立與用戶授權完整操作手冊

前言

表空間是 Oracle 邏輯儲存單元，合理規劃表空間 (永久/臨時/UNDO) 並為應用用戶指定專屬表空間，是效能隔離、空間管控、備份恢復粒度控制的基礎。本文以 Windows/Linux 通用語法，示範表空間建立/修改/刪除、用戶建立與表空間關聯、權限授予/回收全流程。

一、表空間建立

1.1 先決條件：目錄預建

# Linux
mkdir -p /opt/oracle/oradata/cocis
chown oracle:oinstall /opt/oracle/oradata/cocis

# Windows
# 手動建立 D:\Oracle\oradata\cocis

⚠️ 若目錄不存在，CREATE TABLESPACE 會報 ORA-01119/ORA-27040。

1.2 標準建立語法

CREATE TABLESPACE histdb
DATAFILE 'D:\Oracle\product\10.2.0\oradata\orcl\histdb.dbf'  -- 路徑含檔名
SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

1.3 關鍵子句解析

1.4 事後調整範例

-- 手動擴展/縮小資料檔
ALTER DATABASE DATAFILE '.../histdb.dbf' RESIZE 500M;

-- 開啟/關閉自動擴展
ALTER DATABASE DATAFILE '.../histdb.dbf' AUTOEXTEND ON/OFF;

二、表空間刪除

DROP TABLESPACE histdb INCLUDING CONTENTS AND DATAFILES;

• INCLUDING CONTENTS：刪除表空間內所有段 (表、索引等)。
• AND DATAFILES：同步刪除作業系統層面資料檔 (10g+ 支援)。
• 若表空間非空且未加 INCLUDING CONTENTS，將報錯。

三、建立應用用戶並指定預設/臨時表空間

CREATE USER cocis IDENTIFIED BY cocis
DEFAULT TABLESPACE cocis
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON cocis;  -- 10g 可直接在 CREATE USER 給配額

3.1 驗證用戶屬性

SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE username = 'COCIS';

四、權限授予策略

4.1 角色授予 (標準應用用戶)

GRANT CONNECT, RESOURCE TO cocis;

• CONNECT：含 CREATE SESSION 等基本權限。
• RESOURCE：含 CREATE TABLE、CREATE SEQUENCE、CREATE PROCEDURE 等物件建立權限。
• 12c 起 RESOURCE 不再含 UNLIMITED TABLESPACE，需額外授予配額。

4.2 精細權限授予 (最小權限原則)

GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO cocis;
GRANT CREATE TRIGGER, CREATE TYPE, CREATE SYNONYM TO cocis;
-- 視業務需求增減

4.3 DBA 權限授予 (謹慎！)

GRANT DBA TO cocis;  -- 含所有系統權限，含 SHUTDOWN DATABASE

4.4 SYSDBA/ SYSOPER (實例級，極少授予應用用戶)

GRANT SYSDBA TO cocis;  -- 需密碼檔驗證，極高風險

五、表空間配額管理

5.1 回收 UNLIMITED TABLESPACE 系統權限

REVOKE UNLIMITED TABLESPACE FROM cocis;

5.2 針對特定表空間授予配額

ALTER USER cocis QUOTA UNLIMITED ON cocis;
-- 或限制大小
ALTER USER cocis QUOTA 10G ON cocis;

5.3 查詢用戶配額使用情況

SELECT tablespace_name, max_bytes/1024/1024 AS max_mb, bytes/1024/1024 AS used_mb
FROM dba_ts_quotas
WHERE username = 'COCIS';

六、最佳實踐總結

1. 分離表空間：應用表、索引、LOB、歸檔數據分別建立專屬表空間，便於備份/遷移/效能調優。
2. 命名規範：TBS_<應用>_<類型>，如 TBS_CRM_DATA、TBS_CRM_IDX、TBS_CRM_LOB。
3. 啟用 ASSM + LOCAL：預設即佳，勿用 DICTIONARY/MANUAL。
4. 配額管控：回收 UNLIMITED TABLESPACE，按表空間授予配額，防止單一用戶佔滿磁碟。
5. 監控告警：表空間使用率 > 80% 告警，> 90% 自動擴展或清理。