六分鐘掌握Oracle表空間創建：從查詢空間到自動擴展全流程

創建前準備：摸清家底

-- 1. 查詢現有表空間空閒空間
SELECT tablespace_name, file_id, block_id, bytes/1024/1024 free_mb, blocks
FROM dba_free_space
ORDER BY tablespace_name, file_id;

-- 2. 查詢數據文件分佈與大小
SELECT tablespace_name, file_id, file_name, bytes/1024/1024 size_mb, autoextensible, maxbytes/1024/1024 max_mb
FROM dba_data_files
ORDER BY tablespace_name, file_id;

核心創建語法大全

1. 基礎永久表空間（本地管理+自動擴展）

CREATE TABLESPACE app_data
  DATAFILE '/u01/oradata/PROD/app_data01.dbf' SIZE 500M
  AUTOEXTEND ON NEXT 100M MAXSIZE 10G
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

2. 統一區大小（適用大批量載入/數據倉庫）

CREATE TABLESPACE dw_data
  DATAFILE '/u02/oradata/PROD/dw_data01.dbf' SIZE 1G
  AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

3. 臨時表空間（字典管理/本地管理）

-- 10g+ 推薦本地管理
CREATE TEMPORARY TABLESPACE temp_data
  TEMPFILE '/u01/oradata/PROD/temp_data01.dbf' SIZE 2G
  AUTOEXTEND ON NEXT 100M MAXSIZE 10G
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

-- 9i 風格字典管理（不推薦新建）
CREATE TABLESPACE temp_old
  DATAFILE '/u01/oradata/PROD/temp_old01.dbf' SIZE 1G
  TEMPORARY
  EXTENT MANAGEMENT DICTIONARY;

4. 大文件表空間（Bigfile，簡化文件管理）

CREATE BIGFILE TABLESPACE big_data
  DATAFILE '/u03/oradata/PROD/big_data01.dbf' SIZE 50G
  AUTOEXTEND ON NEXT 10G MAXSIZE 100G;

創建後常見操作

調整自動擴展屬性

-- 關閉自動擴展
ALTER DATABASE DATAFILE 
  '/u01/oradata/PROD/app_data01.dbf',
  '/u01/oradata/PROD/app_data02.dbf'
  AUTOEXTEND OFF;

-- 開啟並調整步長
ALTER DATABASE DATAFILE '/u01/oradata/PROD/app_data01.dbf'
  AUTOEXTEND ON NEXT 200M MAXSIZE 20G;

增加數據文件（擴容/多磁盤分散I/O）

ALTER TABLESPACE app_data ADD DATAFILE
  '/u02/oradata/PROD/app_data02.dbf' SIZE 1G
  AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

重命名/遷移數據文件

ALTER TABLESPACE app_data OFFLINE;
-- OS層面 mv /cp 文件
ALTER TABLESPACE app_data RENAME DATAFILE 
  '/old/path/app_data01.dbf' TO '/new/path/app_data01.dbf';
ALTER TABLESPACE app_data ONLINE;

創建用戶並綁定表空間

CREATE USER app_user IDENTIFIED BY "AppUs3rP@ss"
  DEFAULT TABLESPACE app_data
  TEMPORARY TABLESPACE temp_data
  QUOTA UNLIMITED ON app_data;

GRANT CONNECT, RESOURCE TO app_user;
-- 視業務授予 CREATE VIEW, CREATE PROCEDURE 等

避坑清單

1. 文件路徑必須實例可訪問（掛載點權限、ASM磁盤組掛載）。
2. AUTOEXTEND建議開啟+MAXSIZE上限，防單文件撐爆磁盤導致實例掛起。
3. UNIFORM SIZE建議≥1M（小表空間可512K），過小導致區映射表膨脹。
4. BIGFILE表空間僅支援單文件，不可ADD DATAFILE，擴容只能RESIZE。
5. 臨時表空間僅用TEMPFILE，不產生Redo，不可存永久對象。
6. SEGMENT SPACE MANAGEMENT AUTO（ASSM）為默認且推薦，避免FREELIST爭用。

一鍵巡檢腳本

SET LINES 200 PAGES 1000
COL tablespace_name FOR A20
COL file_name FOR A50
SELECT 
  t.tablespace_name,
  t.contents,
  t.extent_management,
  t.allocation_type,
  t.segment_space_management,
  d.file_name,
  ROUND(d.bytes/1024/1024,2) size_mb,
  d.autoextensible,
  ROUND(d.maxbytes/1024/1024,2) max_mb,
  ROUND(NVL(f.free_mb,0),2) free_mb
FROM dba_tablespaces t
JOIN dba_data_files d ON t.tablespace_name = d.tablespace_name
LEFT JOIN (
  SELECT tablespace_name, SUM(bytes)/1024/1024 free_mb
  FROM dba_free_space GROUP BY tablespace_name
) f ON t.tablespace_name = f.tablespace_name
ORDER BY t.tablespace_name, d.file_id;

總結

表空間創建核心公式：查詢現狀 → 選擇類型(永久/臨時/大文件) → 定義文件路徑/大小/自動擴展 → 選擇區管理(AUTOALLOCATE/UNIFORM) → 啟用ASSM → 創建用戶綁定。將上述腳本模板化，新項目交付5分鐘完成表空間佈局。