Oracle 9i 資料庫設計全規範：物理/邏輯/備份/調優一站式指南

前言

資料庫設計品質直接決定系統的效能、可擴展性與維護成本。本文匯總 Oracle 9i 階段的企業級設計規範，涵蓋物理環境規劃、邏輯模型規範、命名標準、SQL 編寫準則、備份恢復策略、系統調優知識庫與設計工具鏈，旨在為架構師、DBA、開發人員提供一份可落地的標準化參考。

一、資料庫物理設計原則

1.1 作業系統與硬體環境選型

• 中小型系統：優先 Linux (x86_64)，成本效益最佳。
• 高可用/負載均衡：採用 Oracle 9i RAC，叢集節點 2–64 節點。
• 大型關鍵業務：Sun Solaris SPARC 64-bit 或 HP 9000 系列小型機。
• 存儲 RAID 級別：
   • RAID 5：適合唯讀/數據倉庫
   • RAID 1 (或 RAID 10)：適合 OLTP 高寫入場景

1.2 記憶體與交換區規劃

警示：SGA 過大導致 OS 分頁，反而拖垮效能；9i 引入動態 SGA，可線上調整 db_cache_size、shared_pool_size 等參數。

1.3 核心資料庫參數標準

• SID：≤ 5 字元，字母開頭；RAC 節點自動衍生 SID1、SID2…
• 全域資料庫名：SID.DOMAIN (如 PROD.MYCOMPANY.COM)
• 資料庫類型：數據倉庫選 Data Warehouse；OLTP 選 Transaction Processing。
• 連接模式：預設採用 MTS (Shared Server)；批次作業可透過專用服務器連接。
• 字符集：統一採用 UTF8 (AL32UTF8) 支援多語言；若需變更字符集，需經 SHUTDOWN → STARTUP MOUNT → ALTER DATABASE CHARACTER SET INTERNAL_USE … 流程，且無 LOB 物件時風險較低。
• DB_BLOCK_SIZE：
   • 數據倉庫：8K/16K
   • OLTP：2K/4K
   • 建議為 OS 區塊大小倍數 (Linux 4K → 8K 通用)
• 控制檔：多路徑鏡像，MAXDATAFILES ≥ DB_FILES。
• REDO 日誌：
   • 批次系統：每組數百 MB–GB，5–10 組
   • OLTP：每組數百 MB 內，3–5 組
   • 每組 2 成員 (鏡像)，目標 20 分鐘切換一次
• Undo 表空間 (9i 新特性)：
   公式 UndoSpace = UR × UPS × DB_BLOCK_SIZE + 冗餘
   例：保留 2 小時、每秒 200 區塊、8K block → 約 5.8 GB
• 臨時/系統表空間：臨時表空間啟用自動擴展；系統表空間約 1 GB，嚴禁存放業務物件。

1.4 表空間設計規範

• 大小估算：Tablespace = SUM(數據段+索引段) × 1.5
• 自動擴展：AUTOEXTEND ON NEXT 2M/4M/8M… MAXSIZE UNLIMITED (建議上限 2G)
• 本地管理 + 自動段空間管理：
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
• NOLOGGING：建表空間/大量載入時指定，減少 REDO 產生。

CREATE TABLESPACE TBS_USERINFO
DATAFILE '/oradata/tbs_userinfo.dbf' SIZE 8M REUSE
AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED
NOLOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

1.5 裸裝置應用

效能提升 ~15%，Unix 採軟連結 ln -s /dev/raw/raw1 /oradata/raw1.dbf 並在建表空間加 REUSE；Windows 使用 Oracle 提供的裸裝置映射工具。

二、資料庫邏輯設計原則

2.1 命名規範總覽

2.2 通用命名原則

• 語言：英文單詞/縮寫，禁用拼音、中文、特殊字元。
• 大小寫：全部大寫，利於跨平台移植與程式呼叫。
• 分隔：單詞間以下劃線 _ 分隔。
• 保留字：嚴禁使用 SQL 保留字。
• 長度：表/欄位/視圖名 ≤ 20 字元 (含前綴)。
• 一致性：同義欄位跨表同名同類型；異義欄位禁用同名。

2.3 資料類型選擇指南

• 字串：固定長 CHAR，可變長 VARCHAR2；避免可變長用 CHAR 造成空格填充與 TRIM() 開銷。
• 數值：統一 NUMBER，必要時指定精度 NUMBER(12,2)。
• 日期時間：
   • 系統產生：DATE / TIMESTAMP
   • 外部匯入：VARCHAR2(14) 存 YYYYMMDDHH24MISS，應用層轉換
• 大物件：無特殊需求禁用 BLOB/CLOB/LONG。
• 代理鍵：優先使用 SEQUENCE 產生。

2.4 表與索引設計細則

• 正規化：預設達 3NF；經效能證實必要時適度反正規化，禁用單欄位存多標誌位 (如 11101)。
• 表屬性：
   • 必須指定 TABLESPACE，忙碌表用專用表空間
   • PCTUSED：低更新表 60–85，高更新 40–70
   • INITRANS：並行/RAC 表設 2 的倍數
   • STORAGE：INITIAL 接近預估段大小 (2 的冪)；NEXT 同理；PCTINCREASE=0；MINEXTENTS=1–2
• 分區表：
   • 範圍分區：按時間/數值範圍增長
   • 列表分區：離散關鍵值分布
   • Hash/子分區：靜態均衡或復合場景
• 叢集表：緊密關聯的靜態表可考慮。
• 完整性約束：
   • 父表必有 PK (非空+唯一)，大表指定索引表空間
   • FK 依業務決定是否建立；大表 FK 必建索引；級聯刪除加 ON DELETE CASCADE
   • NOT NULL 顯式宣告；應用層用 NVL() 防空值運算陷阱
   • CHECK 約束用於業務規則驗證
   • 觸發器用於複雜完整性，注意 BEFORE/AFTER 與應用事務一致性，大量導入時禁用
• 註釋：表/欄位必加中文 COMMENT，USER_TAB_COMMENTS 可查。
• 索引策略：查詢條件欄位優先；復合索引高選擇性/高頻欄位前置；分區表優先 LOCAL 索引；非分區索引必指定 TABLESPACE 與 STORAGE。

2.5 視圖、套件、安全性設計

• 視圖：簡化複雜查詢、行/列級安全、嵌套 ≤ 3 層；大表/增長表改用實體化視圖；避免無條件 SELECT * 及視圖中 ORDER BY。
• 套件：所有 Procedure/Function/Ref Cursor 歸屬 PACKAGE；模組化設計、支援重載、必含異常處理；非資料庫操作禁用 PL/SQL 實現；函數中避免 DML/DDL。
• 安全性：
   • SYS/SYSTEM 強制修改預設密碼，禁建業務物件；鎖定 SCOTT
   • 按應用角色劃分權限 (建立/查詢/更新/刪除)；最小權限原則
   • 跨 Schema 訪問走 SYNONYM
   • 應用層帳號與資料庫帳號分離，防繞過應用直連 DB
   • 密碼全程加密存儲，杜絕明文

2.6 SQL 編寫規範

規則	說明
字串字面值	統一單引號；純數字字串必加引號，防隱性轉換導致索引失效
避免 IN / NOT IN	大表多值改用 EXISTS / JOIN；CHAR 比 VARCHAR2 查詢快 (存儲反之)
避免嵌套 SELECT	子查詢改寫為 JOIN 或分析函數
禁用 SELECT *	明確列出欄位 (不含 COUNT(*))
避免無謂排序	ORDER BY 僅在必要時使用
INSERT 必列欄位	防結構變更導致錯誤
COUNT 差異	COUNT(*)=總行數；COUNT(col)=非空行數；COUNT(DISTINCT col)=去重行數
FK 可空控制	無業務需求時 FK 欄位加 NOT NULL
SEQUENCE 跳號	回滾/崩潰/CACHE 導致跳號，不可作連續流水號
集合運算 ORDER BY	欄位名需一致，否則用位置序號 ORDER BY 1,2
WHERE 條件驗證	UPDATE/DELETE 必先用 SELECT 驗證結果集
級聯刪除/轉移	父子表刪除必級聯或按業務轉移

三、備份恢復設計原則

3.1 EXP/IMP 邏輯備份 (輔助手段)

• 資料庫級：小庫/初建庫，不適合 7×24
• 用戶級：小用戶/初建
• 表級：參數表、靜態表、分區表單分區

3.2 冷備份

適用：小庫、可停機窗口。備份：所有數據文件、控制文件、REDO、INIT.ORA。

3.3 RMAN 熱備份 (7×24 首選)

• Catalog 庫：獨立實例，網路暢通；9i 可用控制檔記錄 (CONTROL_FILE_RECORD_KEEP_TIME=365)。
• 歸檔日誌：確保空間足夠，定期全備份並刪除冗餘歸檔。
• 備份策略：
   • 小庫：全備份
   • 大庫：全備份 + 增量/累積備份 週期混合
• 恢復類型：局部 (表空間/文件)、完全 (到故障點)、不完全 (時間點/備份點/RESETLOGS)。

-- 典型 RMAN 全備份腳本片段
BACKUP SPFILE FORMAT '/backup/%d_SPFILE_%T_%s_%p.bak';
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP ARCHIVELOG ALL FORMAT '/backup/%d_ARC_%T_%s_%p.bak' DELETE ALL INPUT;
BACKUP CURRENT CONTROLFILE FORMAT '/backup/%d_CTL_%T_%s_%p.bak';

3.4 Data Guard 物理備庫

適用：中等規模、嚴格 7×24、異地容災需求。

3.5 實戰小撇步

• OEM 排程需 OS 批次登入權限與 DBA 權限雙重授權。
• Agent 啟動失敗常因 $ORACLE_HOME/network/agent/*.q 殘留文件，手動刪除即可。
• ALTER TABLE … MOVE TABLESPACE … 遷移表空間後必重建索引；或用 IMP 限制目標用戶配額實現遷移。
• ALTER SYSTEM SET LOG_CHECKPOINT_TO_ALERT=TRUE 記錄檢查點資訊至 Alert Log。

四、系統調優知識庫

4.1 Statspack / AWR 前身

• 安裝：@?/rdbms/admin/spcreate.sql 建 PERFSTAT 用戶
• TIMED_STATISTICS=TRUE
• 手動快照：EXEC STATSPACK.SNAP 或 EXEC STATSPACK.SNAP(I_SNAP_LEVEL=>5,...)
• 報表：@?/rdbms/admin/spreport.sql
• 清理/移除：spdrop.sql、DBMS_JOB.REMOVE

4.2 SQL 追蹤與 TKPROF

• 實例級：ALTER SYSTEM SET SQL_TRACE=TRUE
• Session 級：ALTER SESSION SET SQL_TRACE=TRUE 或 DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,TRUE)
• 追蹤文件：UDUMP/*_ora_.trc、系統 BDUMP/alert_.log
• TKPROF 格式化分析；關注 AVG RD(ms) > 20 表空間爭用、FAST_START_MTTR_TARGET、REDO 切換頻率、緩存命中率 > 90%。

4.3 記憶體架構調優

多緩存池策略：DB_CACHE_SIZE (預設)、DB_KEEP_CACHE_SIZE (熱小表)、DB_RECYCLE_CACHE_SIZE (一次性大掃描)。

4.4 優化器模式與統計信息

• OPTIMIZER_MODE：CHOOSE (預設) / ALL_ROWS / FIRST_ROWS_n / RULE
• CBO 需定期 DBMS_STATS.GATHER_TABLE_STATS / ANALYZE TABLE … COMPUTE STATISTICS
• 查看統計：DBA_TABLES 欄位 NUM_ROWS, BLOCKS, AVG_ROW_LEN, CHAIN_CNT

五、設計工具標準化

統一採用 Sybase PowerDesigner 完成物理模型設計；所有物件變更以物理模型為準；生成腳本統一大寫，避免大小寫敏感問題。

結語

本規範涵蓋 Oracle 9i 從硬體選型到 SQL 細節的全鏈路標準，建議專案啟動時發佈為基線，並在評審、代碼審查、上線驗收環節強制執行。隨著版本演進 (10g/11g/12c/19c/23c)，部分參數與工具已更新 (如 AWR 取代 Statspack、ASM 管理存儲、ADG 替代手工備庫)，但設計思想與規範框架仍具普適參考價值。願此文助你構建高效、穩健、可演進的資料庫系統。

1. 上一篇文章： Oracle 核心工具全景速查：從開發到運維的必備工具箱
2. 下一篇文章： Oracle 10g：為網格計算而生的資料庫革命

Oracle 資料庫基礎

Oracle 監聽器與網絡配置圖文教程：Net Manager 實戰指南

架構認知：Listener 與 TNSNAMES 的角色分工

Oracle 自增主鍵實戰：Sequence + Trigger 實現 IDENTITY 效果 (附圖解流程)

業務場景 表 `USERINFO` 需要 `ID` 欄位自動

Oracle 分頁查詢最佳實踐：ROWNUM 經典模式與 12c+ 新特性

Oracle 分頁查詢全解析：從經典三層嵌套到 FETCH

使用 PL/SQL Developer 將遠端 Oracle 資料庫遷移至本地端完整教學

開發測試常需將生產/測試環境的 Schema 匯入本地資料庫

Oracle RMAN 核心功能全解析：備份與恢復的利器 Oracle 資料庫鎖機制全解析：DML/DDL/內部鎖、阻塞與死鎖實戰 Oracle默認用戶名密碼速查手冊：安裝後必改清單與安全基線 Oracle vs SQL Server 交易處理機制深度比較：隔離級別、DDL 影響與連線中斷行為 Spring Framework 呼叫 Oracle REF CURSOR 儲存程序完整指南 Oracle 9i Statspack 安裝、使用與自動化完整指南 Oracle 資料庫體系架構全景圖解：實例、記憶體、進程、邏輯與物理結構深度解析

欄目導航

Oracle 資料庫相關知識

Oracle 資料庫基礎

Oracle 教學

Copyright © 程式碼之家 All Rights Reserved

區域	建議佔比	關鍵指標與調整手段
SGA	50% (80% DB Cache, 15% Shared Pool, 5% Other)	DB_CACHE_HIT_RATIO > 90%；FREE BUFFER WAITS → 加 DB_WRITER_PROCESSES、DISK_ASYNCH_IO=TRUE；BUFFER BUSY WAITS → 擴大 DB_CACHE_SIZE 或分離熱表至 KEEP POOL
Shared Pool	SGA 15%	HARD PARSES < 100/s、SOFT PARSE > 90%；OLTP 用 CURSOR_SHARING=FORCE，DWH 用 EXACT；大 PL/SQL 用 DBMS_SHARED_POOL.KEEP 或增 SHARED_POOL_RESERVED_SIZE (≤ 50%)
PGA	30%	9i WORKAREA_SIZE_POLICY=AUTO + PGA_AGGREGATE_TARGET；排序磁碟/記憶體比 < 5%；專用/共享服務器參數不同
OS	20%	保留核心與網路緩衝