Oracle 刪除使用者下所有物件的兩種方法：CASCADE 參數與 PL/SQL 動態清除

方法一：DROP USER ... CASCADE（標準、最快、原子性）

語法

DROP USER username CASCADE;

執行效果

• 刪除使用者擁有的所有 Schema 物件：表、索引、視圖、序列、程序、函數、套件、觸發器、同義詞、資料庫連結、類型、佇列、材化視圖等
• 刪除使用者本身
• 若使用者擁有表空間，不會刪除表空間（需額外 DROP TABLESPACE）
• 若物件有外鍵被其他 Schema 參考，會報錯 ORA-02449，需先移除外鍵或一併刪除參考方

完整範例

-- 1. 確認物件清單 (刪除前備份)
SELECT object_type, COUNT(*) FROM dba_objects WHERE owner = 'OLD_USER' GROUP BY object_type;

-- 2. 執行刪除
DROP USER old_user CASCADE;

-- 3. 若需同時刪除表空間 (謹慎！)
DROP TABLESPACE old_user_ts INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

優缺點

方法二：PL/SQL 動態清除（彈性、可控、可擴充）

核心邏輯

1. 查詢 USER_OBJECTS / DBA_OBJECTS 取得物件清單
2. 依相依順序排序（類型優先級：VIEW/PROCEDURE → TABLE → INDEX/LOB → CONSTRAINT）
3. 動態產生 DROP 指令並執行
4. 例外處理：記錄失敗、決定是否繼續

完整實作程式碼

CREATE OR REPLACE PROCEDURE drop_user_objects(
  p_username IN VARCHAR2,
  p_keep_types IN VARCHAR2 DEFAULT NULL,  -- 逗號分隔要保留的類型，如 'TABLE,SEQUENCE'
  p_dry_run IN BOOLEAN DEFAULT FALSE      -- TRUE 僅列印不執行
) IS
  v_sql VARCHAR2(4000);
  v_dropped NUMBER := 0;
  v_failed NUMBER := 0;
  v_keep_map BOOLEAN := (p_keep_types IS NOT NULL);
  TYPE t_keep IS TABLE OF BOOLEAN INDEX BY VARCHAR2(30);
  v_keep t_keep;
BEGIN
  -- 解析保留類型
  IF v_keep_map THEN
    FOR tok IN (SELECT TRIM(REGEXP_SUBSTR(p_keep_types, '[^,]+', 1, LEVEL)) AS typ FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT(p_keep_types, ',')+1) LOOP
      v_keep(UPPER(tok.typ)) := TRUE;
    END LOOP;
  END IF;

  DBMS_OUTPUT.PUT_LINE('=== 開始清除使用者 ' || UPPER(p_username) || ' 物件 ===');
  IF p_dry_run THEN DBMS_OUTPUT.PUT_LINE('*** DRY RUN 模式：僅列印不執行 ***'); END IF;

  -- 依相依順序刪除：先移除 CONSTRAINT/INDEX/LOB，再 TABLE，最後其他
  FOR obj_type IN (
    SELECT 'VIEW' AS typ FROM DUAL UNION ALL
    SELECT 'PROCEDURE' FROM DUAL UNION ALL
    SELECT 'FUNCTION' FROM DUAL UNION ALL
    SELECT 'PACKAGE' FROM DUAL UNION ALL
    SELECT 'PACKAGE BODY' FROM DUAL UNION ALL
    SELECT 'TRIGGER' FROM DUAL UNION ALL
    SELECT 'SYNONYM' FROM DUAL UNION ALL
    SELECT 'SEQUENCE' FROM DUAL UNION ALL
    SELECT 'TYPE' FROM DUAL UNION ALL
    SELECT 'TYPE BODY' FROM DUAL UNION ALL
    SELECT 'MATERIALIZED VIEW' FROM DUAL UNION ALL
    SELECT 'DATABASE LINK' FROM DUAL UNION ALL
    SELECT 'CLUSTER' FROM DUAL UNION ALL
    -- 表相關：先 FK/INDEX/LOB，再 TABLE
    SELECT 'INDEX' FROM DUAL UNION ALL
    SELECT 'LOB' FROM DUAL UNION ALL
    SELECT 'TABLE' FROM DUAL
  ) LOOP
    -- 跳過保留類型
    IF v_keep_map AND v_keep.EXISTS(obj_type.typ) THEN
      DBMS_OUTPUT.PUT_LINE('保留類型: ' || obj_type.typ);
      CONTINUE;
    END IF;

    FOR obj IN (
      SELECT object_name
      FROM dba_objects
      WHERE owner = UPPER(p_username)
      AND object_type = obj_type.typ
      AND status != 'INVALID'  -- 選項：跳過無效物件
      ORDER BY object_name
    ) LOOP
      BEGIN
        v_sql := 'DROP ' || obj_type.typ || ' ' || UPPER(p_username) || '.' || obj.object_name;
        
        -- 特殊處理
        IF obj_type.typ = 'TABLE' THEN
          v_sql := v_sql || ' CASCADE CONSTRAINTS PURGE';
        ELSIF obj_type.typ IN ('INDEX','LOB') THEN
          -- INDEX/LOB 隨 TABLE 刪除，若 TABLE 已刪則忽略錯誤
          NULL;
        END IF;

        IF p_dry_run THEN
          DBMS_OUTPUT.PUT_LINE('[DRY RUN] ' || v_sql);
        ELSE
          EXECUTE IMMEDIATE v_sql;
          DBMS_OUTPUT.PUT_LINE('DROPPED: ' || v_sql);
        END IF;
        v_dropped := v_dropped + 1;
        
      EXCEPTION
        WHEN OTHERS THEN
          v_failed := v_failed + 1;
          DBMS_OUTPUT.PUT_LINE('FAILED: ' || v_sql || ' - ' || SQLERRM);
          -- 可選擇 CONTINUE 或 RAISE
      END;
    END LOOP;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('=== 完成：成功 ' || v_dropped || ' 失敗 ' || v_failed || ' ===');
  
  IF v_failed > 0 AND NOT p_dry_run THEN
    RAISE_APPLICATION_ERROR(-20001, '部分物件刪除失敗，請檢查日誌');
  END IF;
END drop_user_objects;
/

使用範例

-- 1. 完整清除 (等同 CASCADE)
EXEC drop_user_objects('OLD_USER');

-- 2. 保留表與序列 (遷移場景)
EXEC drop_user_objects('OLD_USER', 'TABLE,SEQUENCE');

-- 3. Dry Run 先預覽
EXEC drop_user_objects('OLD_USER', NULL, TRUE);

-- 4. 僅清除特定類型 (修改程式碼或加參數)

進階版：支援 PURGE、平行處理、詳細日誌

CREATE OR REPLACE PROCEDURE drop_user_objects_adv(
  p_username IN VARCHAR2,
  p_purge IN BOOLEAN DEFAULT TRUE,
  p_parallel IN NUMBER DEFAULT 1,  -- 1=序列，>1 需額外實作 DBMS_SCHEDULER
  p_log_table IN VARCHAR2 DEFAULT 'DROP_OBJECT_LOG'
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;  -- 記錄日誌不受主交易影響
BEGIN
  -- 確保日誌表存在
  BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE ' || p_log_table || ' (
      log_id NUMBER GENERATED ALWAYS AS IDENTITY,
      username VARCHAR2(128),
      object_type VARCHAR2(30),
      object_name VARCHAR2(128),
      ddl_sql VARCHAR2(4000),
      status VARCHAR2(20),
      error_msg VARCHAR2(4000),
      log_time TIMESTAMP DEFAULT SYSTIMESTAMP
    )';
  EXCEPTION WHEN OTHERS THEN NULL; END;

  -- ... 核心刪除邏輯同前，加入 INSERT INTO log_table ...
  COMMIT;  -- 自主交易提交日誌
END;
/

特殊情況處理

選擇建議

單純刪除測試/舊使用者 → DROP USER ... CASCADE (最簡單、最快)

需要保留部分物件 → PL/SQL 動態清除 (指定 p_keep_types)

需要審計追蹤/詳細日誌 → 進階版 PL/SQL (自主交易寫入日誌表)

大量物件 (萬級以上) 關注效能 → CASCADE (內部最佳化) 或分批 PL/SQL + COMMIT

跨 Schema 相依複雜 → 先分析相依圖，手動處理外鍵，再 CASCADE

生產環境變更 → 必須 Dry Run、備份、變更窗口、回滾計畫

驗證清除完成

-- 確認無殘留物件
SELECT object_type, COUNT(*) FROM dba_objects WHERE owner = 'OLD_USER' GROUP BY object_type;

-- 確認使用者不存在
SELECT * FROM dba_users WHERE username = 'OLD_USER';  -- 應無資料

-- 確認表空間配額釋放
SELECT * FROM dba_ts_quotas WHERE username = 'OLD_USER';  -- 應無資料