Oracle 體系架構：儲存管理核心技術詳解

一、表空間進階管理與 10g 新特性

表空間類型完整分類

• SYSTEM：核心字典、系統物件，**必須 ONLINE**
• SYSAUX：輔助表空間，支援 LogMiner、統計、Advisor 等工具，分擔 SYSTEM 壓力
• UNDO：回滾段表空間，**必須 ONLINE**，儲存 Undo 資料保障一致性讀
• TEMP：臨時表空間，排序、雜湊、全域暫存表
• 應用表空間：使用者資料/索引/LOB 等業務物件

查詢資料庫預設屬性

SELECT * FROM database_properties;
-- 顯示：預設永久表空間、預設臨時表空間、預設表空間類型等

建立表空間完整語法

-- 永久表空間 (採用 ASSM 自動段空間管理)
CREATE TABLESPACE ts_data 
DATAFILE '/u01/oradata/ts_data01.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE  -- 或 UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;         -- 或 MANUAL

-- 臨時表空間 (僅支援 UNIFORM + MANUAL)
CREATE TEMPORARY TABLESPACE ts_temp 
TEMPFILE '/u01/oradata/ts_temp01.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT MANUAL;

設定資料庫預設表空間

-- 預設永久表空間
ALTER DATABASE DEFAULT TABLESPACE ts_data;

-- 預設臨時表空間
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE ts_temp;

二、臨時表空間組 — 10g 新特性

解決問題

同一使用者多 Session 爭用單一臨時表空間導致分配鎖競爭。

建立與管理

-- 建立多個臨時表空間
CREATE TEMPORARY TABLESPACE temp1 TEMPFILE ...;
CREATE TEMPORARY TABLESPACE temp2 TEMPFILE ...;

-- 加入群組
ALTER TABLESPACE temp1 TABLESPACE GROUP grp_temp;
ALTER TABLESPACE temp2 TABLESPACE GROUP grp_temp;
-- 自動產生 DBA_TABLESPACE_GROUPS 字典

-- 指派給使用者
ALTER USER scott TEMPORARY TABLESPACE grp_temp;

-- 設為資料庫預設
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE grp_temp;

-- 移出群組
ALTER TABLESPACE temp1 TABLESPACE GROUP '';

V$TEMPFILE vs DBA_TEMP_FILES

視圖	資料來源	可讀狀態	用途
V$TEMPFILE	控制檔	MOUNT 即可讀	恢復階段檢查
DBA_TEMP_FILES	資料字典 (資料檔)	OPEN 才可讀	日常管理查詢

設計雙視圖是為了**恢復階段也能檢查臨時檔狀態**。

三、SYSAUX 表空間佔用者查詢

SELECT * FROM v$sysaux_occupants;
-- 顯示各工具組件佔用空間、結構描述、移動程序等

四、資料區塊大小策略與快取架構

標準 vs 非標準區塊

• 標準區塊：大小 = DB_BLOCK_SIZE (預設 8KB)
• 非標準區塊：大小 ≠ DB_BLOCK_SIZE (2K/4K/16K/32K)

區塊大小權衡

區塊大小	優點	缺點	適用場景
小 (2K/4K)	單區塊記錄少，併發競爭低	查詢掃描區塊多，速度慢	高併發 OLTP、小行長度表
大 (16K/32K)	掃描效率高，索引高度低	併發競爭劇烈，空間浪費	DSS、數據倉庫、大行/LOB 物件

快取架構：複合型快取

當資料庫包含非標準區塊物件時，SGA 必須配置對應大小的快取：

-- 查看現有快取大小參數
SHOW PARAMETER cache_size;

-- 動態新增 32K 快取
ALTER SYSTEM SET DB_32K_CACHE_SIZE = 20M;

此時 SGA 同時存在 8K (預設) + 32K (新增) 兩種快取 → **複合型快取**。

區塊層級剖析工具

使用 DUMP 指令剖析區塊內部結構：

ALTER SYSTEM DUMP DATAFILE 4 BLOCK 1234;
-- 產生追蹤檔於 USER_DUMP_DEST，可分析區塊頭、行目錄、行資料、ITL 槽等

五、物件管理核心技術

表分類與特性

類型	特點	適用場景
Normal (堆組織表)	標準表，無序存放	通用
Partitioned Table	分區維護、分區剪枝提升查詢	大表、歷史資料、滾動窗口
IOT (Index Organized Table)	資料儲存在索引結構中 (B-tree)	主鍵查詢為主、查閱表
Cluster Table	相關表共享區塊，減少 Join 代價	固定關聯、頻繁 Join 的主從表

表關鍵儲存屬性 (DBA_TABLES)

• LOGGING：預設寫 Redo，可設 NOLOGGING 減少大量載入 Redo 量
• PCTFREE / PCTUSED：區塊空間管理閾值
• INITRANS / MAXTRANS：ITL 槽初始/最大值，**槽滿會報錯**

建表指定交易槽

CREATE TABLE aaa (a INT) MAXTRANS = 3;  -- 限制最大併發交易數

Rowid 邏輯地址結構

前 6 位  : 物件編號 (DBA_OBJECTS)
7-9 位   : 相對檔號 (DBA_DATA_FILES / V$DATAFILE)
10-15 位 : 區塊號 (針對資料檔)
16-18 位 : 區塊內行號

Rowid 定位機制

1. 查詢索引 → 取得 Rowid
2. 解析 Rowid → 定位檔案、區塊、行號
3. 若行已遷移 → 原位置存「遷移指標」 → 跟隨指標找新位置

六、表碎片整理三大方案對比

方案	版本	原理	鎖定	索引	觸發器
MOVE	8i/9i	重建表段	全表排他鎖	需手動 REBUILD	觸發
SHRINK SPACE	10g+	排序 + 釋放高水位下空間	行級鎖 (線上)	需 REBUILD	**不觸發**
EXP/IMP	所有	匯出匯入重建	視模式	隨表重建	觸發

SHRINK 實操流程 (10g+ 推薦)

-- 1. 啟用行移動 (必要條件)
ALTER TABLE test1 ENABLE ROW MOVEMENT;

-- 2. 壓縮空間 (不降低高水位，線上可執行)
ALTER TABLE test1 SHRINK SPACE COMPACT;

-- 3. 釋放空間 (降低高水位)
ALTER TABLE test1 SHRINK SPACE;

七、大表清空與空間釋放策略

DELETE vs TRUNCATE

操作	Redo/Undo	高水位	速度	可回滾
DELETE	大量產生	不變	慢	是
TRUNCATE	極少 (字典操作)	可選保留/釋放	極快	否 (隱含 COMMIT)

最佳實踐流程

-- 1. 先 TRUNCATE (釋放區段或保留)
TRUNCATE TABLE big_table REUSE STORAGE;  -- 保留高水位
-- 或
TRUNCATE TABLE big_table DROP STORAGE;   -- 釋放空間

-- 2. 系統閒置時進一步釋放未使用區段
ALTER TABLE big_table DEALLOCATE UNUSED KEEP 100M;  -- 保留 100M
ALTER TABLE big_table DEALLOCATE UNUSED KEEP 0;     -- 完全釋放

-- 3. 建表指定 NOLOGGING 減少大量載入 Redo
CREATE TABLE big_table NOLOGGING AS SELECT * FROM source_table;

八、索引基礎與存取路徑差異

無索引：全表掃描

SELECT ename FROM emp WHERE empno = 7369;
-- 即使找到第一筆，仍需掃描全表確認無其他符合行

有索引：索引範圍掃描 → TABLE ACCESS BY INDEX ROWID

CREATE INDEX idx_emp_empno ON emp(empno);
-- 查詢流程：
-- 1. 索引範圍掃描定位 ROWID
-- 2. 透過 ROWID 直接存取表區塊
-- 3. 找到唯一行即停止 (UNIQUE INDEX)

儲存管理核心在於：依負載特徵選擇區塊大小、善用多快取/多池、掌握線上重組技術、理解 Rowid 定位機制、制定科學的空間回收策略。