Oracle 進階教學 Day 4：備份還原、表空間、序列、視圖完整實戰

一、邏輯備份與還原

1.1 匯出

1.2 匯入

1.3 關鍵參數提醒

• IGNORE=Y：匯入時忽略「物件已存在」錯誤，直接載入資料 (配合 PK/UK 防重複)。
• COMMIT=Y：大量資料每陣列提交，減少 UNDO 壓力。
• INDEXFILE=filename：只產生建索引 SQL 腳本不實際建立，便於調整表空間後手動執行。
• DESTROY=Y：覆蓋現有資料檔 (極度危險，慎用)。

二、表空間管理實戰

2.1 建立表空間

-- 永久表空間 (本地管理、自動擴展)
CREATE TABLESPACE app_data
  DATAFILE '/u01/app/oracle/oradata/app_data01.dbf' SIZE 500M
  AUTOEXTEND ON NEXT 100M MAXSIZE 10G
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

-- 臨時表空間
CREATE TEMPORARY TABLESPACE app_temp
  TEMPFILE '/u01/app/oracle/oradata/app_temp01.dbf' SIZE 200M
  AUTOEXTEND ON NEXT 50M MAXSIZE 5G
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- 索引專用表空間 (分離 I/O)
CREATE TABLESPACE app_idx
  DATAFILE '/u02/app/oracle/oradata/app_idx01.dbf' SIZE 200M
  AUTOEXTEND ON NEXT 50M MAXSIZE 5G
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

2.2 指定表空間建表

CREATE TABLE orders (
  order_id NUMBER PRIMARY KEY USING INDEX TABLESPACE app_idx,
  cust_id  NUMBER,
  amt      NUMBER(10,2),
  CONSTRAINT uk_order_cust UNIQUE (cust_id, order_id) USING INDEX TABLESPACE app_idx
) TABLESPACE app_data;

2.3 表空間維護操作

-- 擴充現有資料檔
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/app_data01.dbf' RESIZE 2G;

-- 新增資料檔至表空間
ALTER TABLESPACE app_data ADD DATAFILE '/u01/app/oracle/oradata/app_data02.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

-- 刪除表空間 (含內容與資料檔)
DROP TABLESPACE app_data INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

2.4 關鍵原則

• 業務表勿放 SYSTEM/SYSAUX：避免系統表空間碎片化、擴容風險、效能干擾。
• 表、索引、LOB、臨時分離：不同 I/O 特徵置於不同表空間/磁碟群，平衡負載。
• 本地管理 + 自動段空間管理 (ASSM)：預設最佳選擇，消除自由列表爭用。
• 啟用大檔案表空間 (Bigfile TBS)：單一資料檔可達 128TB (32K block)，簡化超大資料庫管理。

三、序列

3.1 建立序列

CREATE SEQUENCE seq_order_id
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  NOCYCLE
  CACHE 100
  NOORDER;  -- RAC 環境建議 NOORDER 提升效能

3.2 偽欄位

• seq_order_id.NEXTVAL：取得下一個值並遞增 (每次呼叫遞增一次)。
• seq_order_id.CURRVAL：取得當前 Session 最後一次 NEXTVAL 的值 (必須先呼叫過 NEXTVAL)。

3.3 實戰應用：觸發器自動填充主鍵

CREATE OR REPLACE TRIGGER trg_orders_bir
BEFORE INSERT ON orders
FOR EACH ROW
WHEN (NEW.order_id IS NULL)
BEGIN
  SELECT seq_order_id.NEXTVAL INTO :NEW.order_id FROM dual;
END;
/
-- 測試
INSERT INTO orders (cust_id, amt) VALUES (1001, 123.45);
SELECT * FROM orders;

3.4 修改與刪除

-- 修改 (除 START WITH 外皆可調整)
ALTER SEQUENCE seq_order_id
  INCREMENT BY 10
  MAXVALUE 1000000
  CYCLE
  NOCACHE;

-- 刪除
DROP SEQUENCE seq_order_id;

3.5 避坑指南

• CACHE 導致跳號：非正常關閉 (SHUTDOWN ABORT、Crash) 會丟失快取中的值，若業務要求連續不可跳號，請用 NOCACHE 或 12c+ SCALABLE SEQUENCE。
• RAC 環境 ORDER 會強制全局排序，嚴重影響效能，建議 NOORDER 接受輕微亂序。
• 序列值達 MAXVALUE 且 NOCYCLE 會報錯 ORA-08004，監控 USER_SEQUENCES.LAST_NUMBER 預警。

四、視圖

4.1 建立視圖

CREATE OR REPLACE VIEW v_high_value_orders AS
SELECT o.order_id, o.cust_id, o.amt, c.cust_name
FROM orders o JOIN customers c ON o.cust_id = c.cust_id
WHERE o.amt > 10000
WITH CHECK OPTION;  -- 確保透過視圖修改的資料仍符合 WHERE 條件

4.2 視圖可更新性條件

單一基礎表、無聚合/分組/集合運算/ DISTINCT/ ROWNUM/ 模糊查詢，且包含所有 NOT NULL 無預設值欄位 → 可直接 INSERT/UPDATE/DELETE。複雜視圖需用 INSTEAD OF TRIGGER 實現可更新。

4.3 視圖優勢

1. 安全性：隱藏敏感欄位/列、限制存取條件。
2. 簡化複雜性：封裝多表聯結、複雜計算，應用只查視圖。
3. 邏輯獨立性：基礎表結構變更 (增欄位、重命名) 僅需調整視圖定義，應用端 SQL 不變。
4. 效能：配合 MATERIALIZED VIEW (物化視圖) 實現查詢重寫與預計算。

4.4 刪除視圖

DROP VIEW v_high_value_orders;

綜合實戰：建立應用使用者與完整物件

-- 1. 表空間
CREATE TABLESPACE app_data ...;
CREATE TABLESPACE app_idx ...;
CREATE TEMPORARY TABLESPACE app_temp ...;

-- 2. 使用者
CREATE USER app_user IDENTIFIED BY strong_pwd
  DEFAULT TABLESPACE app_data
  TEMPORARY TABLESPACE app_temp
  QUOTA UNLIMITED ON app_data;

-- 3. 權限
GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE SEQUENCE TO app_user;

-- 4. 核心表
CREATE TABLE app_user.customers (
  cust_id NUMBER PRIMARY KEY USING INDEX TABLESPACE app_idx,
  cust_name VARCHAR2(100) NOT NULL,
  email VARCHAR2(100) UNIQUE
) TABLESPACE app_data;

CREATE TABLE app_user.orders (
  order_id NUMBER PRIMARY KEY USING INDEX TABLESPACE app_idx,
  cust_id NUMBER REFERENCES customers(cust_id),
  amt NUMBER(12,2),
  order_date DATE DEFAULT SYSDATE
) TABLESPACE app_data;

-- 5. 序列 + 觸發器
CREATE SEQUENCE app_user.seq_order_id START WITH 1 CACHE 100;
CREATE OR REPLACE TRIGGER app_user.trg_orders_bir
BEFORE INSERT ON app_user.orders FOR EACH ROW
WHEN (NEW.order_id IS NULL)
BEGIN SELECT app_user.seq_order_id.NEXTVAL INTO :NEW.order_id FROM dual; END;
/

-- 6. 視圖
CREATE OR REPLACE VIEW app_user.v_cust_order_summary AS
SELECT c.cust_id, c.cust_name, COUNT(o.order_id) AS order_cnt, SUM(o.amt) AS total_amt
FROM app_user.customers c LEFT JOIN app_user.orders o ON c.cust_id = o.cust_id
GROUP BY c.cust_id, c.cust_name;

-- 7. 同義詞 (供其他使用者存取)
CREATE PUBLIC SYNONYM v_cust_order_summary FOR app_user.v_cust_order_summary;
GRANT SELECT ON app_user.v_cust_order_summary TO PUBLIC;