Oracle 資料庫核心知識與常見問題解決方案

一、基礎架構核心概念

表空間分類與建立順序

• 系統/非系統表空間、回滾段表空間；使用者/非使用者表空間
• 臨時表空間不含實際資料，恢復僅需刪除重建；系統表空間備份不可用須重建資料庫
• 標準建立順序：建立表空間 → 修改表空間 → 建立回滾段 → 建立使用者授權 → 建立使用者表 → 建立索引

控制檔與日誌檔管理

• 控制檔建議多建數個置於不同磁碟，提升效能
• 線上日誌記錄所有修改，由 LGWR 即時寫入；離線日誌（歸檔）為選配，滿載後切換歸檔
• 三類檔案移動方法 (Unix 平台)：資料檔、控制檔、線上日誌檔

二、核心 DDL 語法範例

1. 建立表空間

CREATE TABLESPACE test 
DATAFILE '/dev/test_name1' SIZE 1000M, 
         '/dev/test_name2' SIZE 1000M, 
         '/dev/test_name3' SIZE 1000M 
DEFAULT STORAGE (INITIAL 64K NEXT 64K MAXEXTENTS UNLIMITED PCTINCREASE 50);

2. 修改表空間新增資料檔

ALTER TABLESPACE ts_name1 ADD DATAFILE '/dev/name4' SIZE 1000M;

3. 建立回滾段

CREATE ROLLBACK SEGMENT "RStest" TABLESPACE "Test_name" 
STORAGE (INITIAL 16M NEXT 16M MAXEXTENTS UNLIMITED);

注意：避免建立不同大小回滾段，Oracle 不會自動選擇最適合的。

4. 建立使用者與授權

CREATE USER test_user IDENTIFIED BY test_user 
DEFAULT TABLESPACE Test_name1 TEMPORARY TABLESPACE Test_name2;
GRANT CONNECT, DBA, RESOURCE TO test_user;

5. 建立分區表

CREATE TABLE test_name1 (
  a NUMBER(10) NOT NULL,
  b NUMBER(10),
  c NUMBER(3) DEFAULT 0,
  d NUMBER(3) NOT NULL,
  CONSTRAINT PK_test_user PRIMARY KEY (a) 
    USING INDEX TABLESPACE test_name1 STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)
) PCTFREE 10 TABLESPACE test_name1 STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)
PARTITION BY RANGE (d) (
  PARTITION part000 VALUES LESS THAN (1) TABLESPACE test_name1,
  PARTITION part001 VALUES LESS THAN (2) TABLESPACE test_name1
);

6. 建立索引

CREATE INDEX id_tablename1 ON test_name1 (f2) 
TABLESPACE ts_name STORAGE (INITIAL 500K NEXT 500K PCTINCREASE 0);

三、表空間使用率查詢

SELECT SUBSTR(a.TABLESPACE_NAME,1,10) TablespaceName,
       SUM(a.BYTES/1024/1024) Total_Size,
       SUM(NVL(b.FREE_SPACE1/1024/1024,0)) Free_Space,
       SUM(a.BYTES/1024/1024)-SUM(NVL(b.FREE_SPACE1/1024/1024,0)) Used_Space,
       ROUND((SUM(a.BYTES/1024/1024)-SUM(NVL(b.FREE_SPACE1/1024/1024,0)))*100/SUM(a.BYTES/1024/1024),2) Used_Percent
FROM DBA_DATA_FILES a,
     (SELECT SUM(NVL(BYTES,0)) FREE_SPACE1, FILE_ID FROM DBA_FREE_SPACE GROUP BY FILE_ID) b
WHERE a.FILE_ID = b.FILE_ID(+)
GROUP BY a.TABLESPACE_NAME;