Oracle 使用者與表空間重建標準流程：刪除、建立、授權一條龍

專案部署、環境重置、測試資料刷新常需「刪除舊用戶/表空間 → 重建新用戶/表空間 → 授權」。本文提供標準化、可直接執行的操作腳本與關鍵參數說明，適用 Windows/Linux 環境 (路徑自行調整)。

一、完整操作腳本 (SQL*Plus 執行)

-- 1. 以 SYSDBA 登入
CONN / AS SYSDBA

-- 2. 刪除用戶 (CASCADE 級聯刪除所有物件)
DROP USER eqm CASCADE;

-- 3. 刪除表空間 (含內容、資料檔、外鍵約束)
DROP TABLESPACE eqm INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

-- 4. 若報錯「表空間仍被使用」，查詢佔用段並處理
SELECT DISTINCT owner, segment_name, segment_type
FROM dba_extents
WHERE tablespace_name = 'EQM';
-- 依查詢結果 DROP 相關物件後再執行步驟 3

-- 5. 建立表空間 (自動擴展、本地管理)
CREATE TABLESPACE eqm
  LOGGING
  DATAFILE 'D:\oracle\product\10.2.0\oradata\EQM.DBF'  -- Linux 路徑如 /u01/app/oracle/oradata/EQM.dbf
  SIZE 4096M
  AUTOEXTEND ON NEXT 150M MAXSIZE 20480M
  EXTENT MANAGEMENT LOCAL;

-- 6. 建立暫存表空間 (建議分離)
CREATE TEMPORARY TABLESPACE eqm_temp
  TEMPFILE 'D:\oracle\product\10.2.0\oradata\EQM_TEMP.dbf'
  SIZE 1024M
  AUTOEXTEND ON NEXT 100M MAXSIZE 4096M
  EXTENT MANAGEMENT LOCAL;

-- 7. 建立用戶並指定預設/暫存表空間
CREATE USER eqm IDENTIFIED BY eqm
  DEFAULT TABLESPACE eqm
  TEMPORARY TABLESPACE eqm_temp;

-- 8. 授予 DBA 權限 (生產環境請依最小權限原則精細授權)
GRANT DBA TO eqm;

-- 9. (可選) 授予無限制表空間額度
ALTER USER eqm QUOTA UNLIMITED ON eqm;

二、關鍵參數與選項說明

三、常見問題與對策

• DROP USER 卡住：查詢 V$SESSION 找出持有該用戶物件鎖的 Session，KILL 後再試。
• DROP TABLESPACE 報「檔案不存在」：若 OS 層檔案已被手動刪除，需先 ALTER DATABASE DATAFILE '...' OFFLINE DROP; 再 DROP TABLESPACE ... INCLUDING CONTENTS; (參考文章 49279)。
• 路徑權限：確保 Oracle 進程對 DATAFILE/TEMPFILE 目錄有讀寫權限 (Linux: chown oracle:oinstall、chmod 750)。
• 權限最小化：生產環境勿直接 GRANT DBA，改用角色組合：GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE PROCEDURE, ... 並配合 QUOTA 限制。

四、Linux 環境路徑範例

DATAFILE '/u01/app/oracle/oradata/ORCL/eqm01.dbf'
TEMPFILE '/u01/app/oracle/oradata/ORCL/eqm_temp01.dbf'

建議使用 OMF (Oracle Managed Files) 簡化檔案管理：DB_CREATE_FILE_DEST 參數設定後可省略 DATAFILE 子句。