Oracle 表空間與使用者管理常用指令速查

表空間查詢

-- 所有表空間名稱
SELECT tablespace_name FROM dba_data_files GROUP BY tablespace_name;

-- 表空間大小 (MB)
SELECT tablespace_name, SUM(bytes)/1024/1024 AS size_mb
FROM dba_data_files GROUP BY tablespace_name;

-- 剩餘空間 (MB)
SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
FROM dba_free_space GROUP BY tablespace_name;

-- 表空間使用者分布
SELECT tablespace_name, owner, SUM(bytes)/1024/1024 AS used_mb
FROM dba_segments GROUP BY tablespace_name, owner;

建立表空間

-- 永久表空間，自動擴展
CREATE TABLESPACE app_data
  LOGGING
  DATAFILE '/u01/app/oracle/oradata/app_data01.dbf' SIZE 5G AUTOEXTEND ON NEXT 1G MAXSIZE 30G
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

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

刪除表空間

-- 僅刪除表空間定義 (檔案保留)
DROP TABLESPACE app_data;

-- 連同資料檔與內容一併刪除
DROP TABLESPACE app_data INCLUDING CONTENTS AND DATAFILES;

使用者管理

-- 查詢所有使用者
SELECT username FROM dba_users;

-- 建立使用者並指定預設/臨時表空間
CREATE USER app_user IDENTIFIED BY "StrongPwd#123"
  DEFAULT TABLESPACE app_data
  TEMPORARY TABLESPACE app_temp
  ACCOUNT UNLOCK;

-- 常用授權
GRANT CREATE SESSION TO app_user;          -- 連線權限
GRANT CONNECT, RESOURCE TO app_user;       -- 基本角色 (12c+ CONTAINERS 視情況)
GRANT DBA TO app_user;                     -- 完整 DBA 權限 (生產環境慎用)

-- 刪除使用者 (CASCADE 連同物件)
DROP USER app_user CASCADE;

實務建議

• 永久表空間採用 LOCAL AUTOALLOCATE + AUTO SEGMENT SPACE MANAGEMENT (ASSM)，避免手動管理 PCTFREE/PCTUSED。
• 資料檔啟用 AUTOEXTEND 並設 MAXSIZE 防止撐爆磁碟。
• 臨時表空間使用 UNIFORM SIZE 減少程度分配碎片。
• 定期監控 DBA_FREE_SPACE、DBA_DATA_FILES 設定閾值告警 (如 85%)。
• 12c CDB/PDB 架構下，表空間與使用者建立於 PDB 內，前綴 ALTER SESSION SET CONTAINER = pdb_name;。