Oracle 學習筆記 Day 5：資料完整性、索引管理與權限角色實戰

Oracle 學習筆記 Day 5：資料完整性、索引管理與權限角色實戰

今天的學習重點圍繞三大核心主題：維護資料完整性、管理索引以及管理權限與角色。透過實際案例與 SQL 實作，深入理解 Oracle 資料庫的核心管理機制。

一、維護資料的完整性

資料完整性旨在確保資料庫中的資料符合特定的業務邏輯與規則。在 Oracle 中，實現資料完整性主要有三種方式：約束條件、觸發器、應用程式（存儲過程、函數）。其中，約束條件因易於維護且效能最佳，成為首選方案。

五大核心約束類型

• NOT NULL（非空）：強制欄位必須有值，不允許為 NULL。
• UNIQUE（唯一）：確保欄位值不重複，但允許 NULL 值存在。
• PRIMARY KEY（主鍵）：非空且唯一，唯一識別表中每一行記錄。
• FOREIGN KEY（外鍵）：建立表與表之間的引用關係，維護參考完整性。
• CHECK（檢查）：強制資料必須滿足特定條件（如範圍、格式）。

實戰案例：商店銷售系統表設計

設計三張表：goods（商品）、customer（客戶）、purchase（購買記錄），並套用以下約束：

-- 商品表
CREATE TABLE goods (
    goodsId     CHAR(8) PRIMARY KEY,
    goodsName   VARCHAR2(30),
    unitprice   NUMBER(10,2) CHECK (unitprice > 0),
    category    VARCHAR2(8),
    provider    VARCHAR2(30)
);

-- 客戶表
CREATE TABLE customer (
    customerId  CHAR(8) PRIMARY KEY,
    name        VARCHAR2(50) NOT NULL,
    address     VARCHAR2(50),
    email       VARCHAR2(50) UNIQUE,
    sex         CHAR(2) DEFAULT '男' CHECK (sex IN ('男','女')),
    cardId      CHAR(18)
);

-- 購買表（含外鍵與範圍檢查）
CREATE TABLE purchase (
    customerId  CHAR(8) REFERENCES customer(customerId),
    goodsId     CHAR(8) REFERENCES goods(goodsId),
    nums        NUMBER(10) CHECK (nums BETWEEN 1 AND 30)
);

事後添加/修改/刪除約束

若建表時遺漏約束，可使用 ALTER TABLE 補充：

-- 修改欄位為非空
ALTER TABLE goods MODIFY goodsName NOT NULL;

-- 新增唯一約束並命名
ALTER TABLE customer ADD CONSTRAINT cardunique UNIQUE(cardId);

-- 新增檢查約束
ALTER TABLE customer ADD CONSTRAINT addresscheck CHECK (address IN ('東城','西城'));

-- 刪除約束
ALTER TABLE 表名 DROP CONSTRAINT 約束名稱;

查詢約束資訊

透過資料字典視圖快速掌握約束狀態：

-- 查看表的所有約束
SELECT constraint_name, constraint_type, status, validated
FROM user_constraints
WHERE table_name = '表名';

-- 查看約束對應的欄位
SELECT column_name, position
FROM user_cons_columns
WHERE constraint_name = '約束名';

實務上也可直接使用 PL/SQL Developer 等工具視覺化查看。

表級與列級約束定義差異

• 列級定義：在定義欄位時同步宣告約束，NOT NULL 僅能在列級定義。
• 表級定義：所有欄位定義完成後，統一宣告約束，適用於複合主鍵、外鍵等跨欄位情境。

二、管理索引

索引是加速資料檢索的資料庫物件，合理使用可大幅降低 I/O 次數，提升查詢效能。

索引運作原理

索引類似書本的目錄，透過有序的資料結構（如 B*Tree）快速定位資料實際儲存位置，避免全表掃描。

索引分類與建立

• 單列索引：基於單一欄位。
  CREATE INDEX nameIndex ON customer(name);
• 複合索引：基於多個欄位，欄位順序影響索引效用。
  CREATE INDEX emp_index1 ON emp(ename, job); 與 CREATE INDEX emp_index2 ON emp(job, ename); 是兩個不同索引。

注意：SQL 執行計畫掃描順序通常從右至左，複合索引欄位順序需依查詢條件設計。

索引使用原則

1. 大表（資料量大）優先建立索引。
2. 常用於 WHERE 條件或 JOIN 連接條件的欄位建立索引。
3. 索引層級（B*Tree 高度）建議不超過 4 層。

索引的代價

• 佔用約為表大小 1.2 倍的磁碟與記憶體空間。
• DML 操作（INSERT/UPDATE/DELETE）需額外維護索引，增加寫入開銷。

索引資訊查詢

SELECT * FROM user_indexes WHERE table_name = '表名';
SELECT * FROM dba_indexes; -- 需 DBA 權限

三、管理權限與角色

Oracle 權限體系分為系統權限與物件權限，角色則是權限的集合，用於簡化授權管理。

系統權限

操作資料庫系統本身的權限（如 CREATE SESSION, CREATE TABLE 等，共 140 種）。

• 授予：GRANT create session, create table TO ken WITH ADMIN OPTION;（WITH ADMIN OPTION 允許受權者轉授）
• 回收：REVOKE create session FROM ken;（系統權限不支援級聯回收）

物件權限

操作特定資料庫物件（表、視圖、序列等）的權限（SELECT, INSERT, UPDATE, DELETE, INDEX, REFERENCES, EXECUTE 等）。

• 授予：GRANT SELECT, UPDATE ON emp TO monkey; 或 GRANT ALL ON emp TO monkey;
• 欄位級授予：GRANT UPDATE ON emp(sal) TO monkey; 僅允許修改薪資欄位。
• 轉授選項：WITH GRANT OPTION（僅能授予使用者，不可授予角色）。
• 回收：REVOKE SELECT ON emp FROM monkey;（物件權限支援級聯回收）。

角色管理

預定義角色（約 25 種），常用三大角色：

自定義角色流程：

1. 建立角色：CREATE ROLE myrole1 NOT IDENTIFIED;（無密碼）或 CREATE ROLE myrole2 IDENTIFIED BY pwd;（資料庫驗證）
2. 授予權限給角色：GRANT CREATE SESSION TO myrole1 WITH ADMIN OPTION; / GRANT SELECT ON emp TO myrole1;
3. 將角色指派給使用者：GRANT myrole1 TO along;
4. 刪除角色：DROP ROLE myrole1;（需 SYS/SYSTEM 權限）

常用角色查詢視圖

-- 所有角色
SELECT * FROM dba_roles;

-- 角色擁有的系統權限
SELECT * FROM role_sys_privs WHERE role = '角色名';

-- 角色擁有的物件權限
SELECT * FROM role_tab_privs WHERE role = '角色名';

-- 使用者擁有的角色
SELECT granted_role, default_role FROM dba_role_privs WHERE grantee = 'SCOTT';

精細存取控制

透過函數與策略實現更細粒度的安全存取控制（VPD, Virtual Private Database）。