Oracle 表空間建立、管理與擴充完整操作手冊

一、建立表空間標準腳本

1. 臨時表空間

CREATE TEMPORARY TABLESPACE user_temp
  TEMPFILE 'D:\oracle\oradata\ORCL\user_temp.dbf'
  SIZE 50M
  AUTOEXTEND ON NEXT 50M MAXSIZE 20G
  EXTENT MANAGEMENT LOCAL;

2. 永久資料表空間

CREATE TABLESPACE user_data
  LOGGING
  DATAFILE 'D:\oracle\oradata\ORCL\user_data.dbf'
  SIZE 50M
  AUTOEXTEND ON NEXT 50M MAXSIZE 20G
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

3. UNDO 表空間

CREATE UNDO TABLESPACE undotbs02
  DATAFILE '/oracle/oradata/ORCL/undotbs02.dbf' SIZE 50M;

-- 切換啟用
ALTER SYSTEM SET undo_tablespace = undotbs02;

二、建立使用者並指定預設表空間

CREATE USER username IDENTIFIED BY password
  DEFAULT TABLESPACE user_data
  TEMPORARY TABLESPACE user_temp;

GRANT CONNECT, RESOURCE TO username;

三、表空間狀態控制

四、擴充表空間三種方式

1. 新增資料檔：
   ALTER TABLESPACE tbs ADD DATAFILE 'path' SIZE 1G;
2. 手動調整檔案大小：
   ALTER DATABASE DATAFILE 'path' RESIZE 4G;
3. 啟用自動擴充：
   ALTER DATABASE DATAFILE 'path' AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

五、監控表空間使用率

SELECT a.tablespace_name,
       ROUND(a.bytes/1024/1024) total_mb,
       ROUND(b.bytes/1024/1024) used_mb,
       ROUND(c.bytes/1024/1024) free_mb,
       ROUND(b.bytes*100/a.bytes, 2) pct_used
  FROM (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) bytes FROM dba_segments GROUP BY tablespace_name) b,
       (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) c
 WHERE a.tablespace_name = b.tablespace_name
   AND a.tablespace_name = c.tablespace_name;

六、刪除表空間

-- 僅刪除空表空間
DROP TABLESPACE tbs INCLUDING CONTENTS AND DATAFILES;

-- 刪除使用者及其物件
DROP USER username CASCADE;

七、撤銷權限

REVOKE privilege_list FROM username;