萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 表空間管理完整指南:核心概念、操作語法與實戰案例

一、表空間核心架構

表空間是 Oracle 邏儲存結構的最高層級,物理上由一個或多個資料檔組成。Oracle 11g 預設必建四大系統表空間:

  • SYSTEM:存放資料字典、PL/SQL 程式碼等核心中繼資料。
  • SYSAUX:輔助 SYSTEM,儲存非字典類資料庫元件資料(如 AWR、EM Repository)。
  • TEMP:供大型排序、雜湊連線、索引建立等操作使用的臨時段空間。
  • UNDOTBS1:儲存 Undo 資訊,支援讀取一致性、交易回滾與快照隔離。

邏輯層級關係:表空間 → 段 → 區 → 區塊。一個段僅屬於一個表空間,區不跨資料檔,區塊是最小 I/O 單位。

二、查詢表空間與資料檔對應關係

COL tablespace_name FOR A15
COL file_name FOR A55
COL bytes FOR 999,999,999,999
SELECT tablespace_name, file_name, bytes
FROM dba_data_files
ORDER BY tablespace_name;

三、建立表空間完整語法與參數詳解

CREATE [SMALLFILE | BIGFILE] TABLESPACE tablespace_name
DATAFILE '/path/filename' SIZE size_spec [REUSE]
[ AUTOEXTEND {ON | OFF} [NEXT size_spec] [MAXSIZE {UNLIMITED | size_spec}] ]
[ MINIMUM EXTENT size_spec ]
[ DEFAULT STORAGE storage_clause ]
[ ONLINE | OFFLINE ]
[ LOGGING | NOLOGGING ]
[ PERMANENT | TEMPORARY ]
[ EXTENT MANAGEMENT {DICTIONARY | LOCAL [AUTOALLOCATE | UNIFORM SIZE size_spec] } ];

關鍵參數說明

參數說明
SMALLFILE/BIGFILE小檔案表空間(預設,最多 1022 檔)vs 大檔案表空間(單檔,適合超大資料庫)
AUTOEXTEND啟用自動擴展,配合 NEXT 指定每次增長量,MAXSIZE 限制上限
MINIMUM EXTENT區的最小尺寸,需為區塊大小整數倍,減少碎片
EXTENT MANAGEMENT LOCAL本地管理(預設),位圖追蹤空閒空間,效能優於字典管理
AUTOALLOCATE / UNIFORM SIZE系統自動分配區大小 / 固定區大小(預設 1M)
REUSE檔案存在則覆寫,不存在則建立

四、刪除表空間的四種模式與級聯約束

-- 僅刪除空表空間,保留資料檔
DROP TABLESPACE ts_name;

-- 刪除非空表空間,保留資料檔
DROP TABLESPACE ts_name INCLUDING CONTENTS;

-- 刪除空表空間並移除資料檔
DROP TABLESPACE ts_name INCLUDING DATAFILES;

-- 刪除非空表空間並移除資料檔
DROP TABLESPACE ts_name INCLUDING CONTENTS AND DATAFILES;

-- 存在跨表空間外鍵約束時,需加 CASCADE CONSTRAINTS
DROP TABLESPACE ts_name INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
注意:指定 DATAFILES 時必須同時包含 CONTENTS 關鍵字,否則報錯。

五、實戰案例演示

案例 1:建立並刪除空表空間

CREATE TABLESPACE exampletb
DATAFILE 'E:\examp01.dbf' SIZE 5M AUTOEXTEND ON NEXT 128K MAXSIZE 1000M,
       'E:\examp02.dbf' SIZE 5M AUTOEXTEND ON NEXT 128K MAXSIZE 1000M;
DROP TABLESPACE exampletb;

案例 2:建立含資料表的表空間並連帶刪除

CREATE TABLESPACE exampletb
DATAFILE 'E:\examp01.dbf' SIZE 5M AUTOEXTEND ON NEXT 128K MAXSIZE 1000M REUSE,
       'E:\examp02.dbf' SIZE 5M AUTOEXTEND ON NEXT 128K MAXSIZE 1000M REUSE;
CREATE TABLE scott.student (id NUMBER, name VARCHAR2(10)) TABLESPACE exampletb;
DROP TABLESPACE exampletb INCLUDING CONTENTS;

案例 3:跨表空間外鍵級聯刪除

CREATE TABLESPACE exampletb1 DATAFILE 'E:\examp03.dbf' SIZE 5M AUTOEXTEND ON NEXT 128K MAXSIZE 1000M;
CREATE TABLESPACE exampletb2 DATAFILE 'E:\examp04.dbf' SIZE 5M AUTOEXTEND ON NEXT 128K MAXSIZE 1000M;
CREATE TABLE test1 (mobile NUMBER(13) PRIMARY KEY) TABLESPACE exampletb1;
CREATE TABLE test2 (mobile NUMBER(13) REFERENCES test1(mobile)) TABLESPACE exampletb2;
DROP TABLESPACE exampletb1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

六、查詢表空間內物件分布

COL owner FOR A10
COL segment_name FOR A30
COL segment_type FOR A20
SELECT segment_type, segment_name, owner
FROM dba_segments
WHERE tablespace_name = 'USERS';

七、SYSAUX 表空間佔用分析

SELECT owner AS 用戶, COUNT(segment_name) AS 物件數量
FROM dba_segments
WHERE tablespace_name = 'SYSAUX'
GROUP BY owner;

合理規劃表空間大小、自動擴展策略與區管理模式,能有效避免 ORA-01658 空間不足錯誤,並簡化後續維護作業。

copyright © 萬盛學電腦網 all rights reserved