Oracle表空間全生命周期SQL速查手冊

建立表空間

-- 永久表空間，指定Extent統一大小128K（默認64K）
CREATE TABLESPACE data01
DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M
UNIFORM SIZE 128K;

-- UNDO表空間（僅一個可處於活躍狀態）
CREATE UNDO TABLESPACE UNDOTBS02
DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M;
-- 切換啟用
ALTER SYSTEM SET undo_tablespace = UNDOTBS02;

-- 臨時表空間
CREATE TEMPORARY TABLESPACE temp_data
TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M;

狀態控制

-- 表空間離線/上線
ALTER TABLESPACE game OFFLINE [FOR RECOVER];
ALTER TABLESPACE game ONLINE;

-- 數據文件離線/上線
ALTER DATABASE DATAFILE 3 OFFLINE;
ALTER DATABASE DATAFILE 3 ONLINE;

-- 唯讀/讀寫切換
ALTER TABLESPACE game READ ONLY;
ALTER TABLESPACE game READ WRITE;

空間擴展三板斧

-- 1. 新增數據文件
ALTER TABLESPACE game
ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;

-- 2. 手動Resize現有文件
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf' RESIZE 4000M;

-- 3. 啟用自動擴展（推薦）
ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 10000M;

移動表至新表空間

ALTER TABLE my_table MOVE TABLESPACE room1;
-- 同步重建索引
ALTER INDEX my_idx REBUILD TABLESPACE idx_tbs;

刪除表空間（連帶數據文件）

DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

監控查詢：表空間使用率

SELECT A.TABLESPACE_NAME,
       A.BYTES TOTAL,
       B.BYTES USED,
       C.BYTES FREE,
       ROUND(B.BYTES*100/A.BYTES,2) "% USED",
       ROUND(C.BYTES*100/A.BYTES,2) "% FREE"
FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
  AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;

先決查詢：查看文件分佈

SELECT tablespace_name, file_id, file_name,
       ROUND(bytes/1024/1024,0) total_mb
FROM dba_data_files
ORDER BY tablespace_name;

最佳實踐：
• 生產環境全域啟用AUTOEXTEND + MAXSIZE防止寫入失敗
• 定期運行使用率查詢，>80%預警擴容
• UNDO/TEMP表空間單獨規劃磁盤，隔離I/O爭用