Oracle 刪除用戶下所有物件的兩種高效方法：CASCADE 與 PL/SQL 動態腳本

方法一：一條指令搞定（推薦生產環境）

DROP USER username CASCADE;
DROP TABLESPACE user_tbs INCLUDING CONTENTS AND DATAFILES;

• CASCADE：遞歸刪除用戶擁有的所有 Schema 物件（表、視圖、索引、序列、程序、觸發器、類型、同義詞等）。
• INCLUDING CONTENTS AND DATAFILES：同時刪除表空間內資料與作業系統層級資料檔，徹底釋放空間。
• 優點：原子性、極快、無遺漏、官方支援。
• 注意：若該用戶物件被其他用戶引用（如外鍵、同義詞），需先處理依賴關係。

方法二：PL/SQL 動態腳本（精細控制、特殊需求）

適用場景：不想刪除用戶、只清空特定類型物件、需記錄刪除日誌、避開外鍵依賴順序問題。

DECLARE
    TYPE name_arr IS TABLE OF VARCHAR2(128);
    TYPE type_arr IS TABLE OF VARCHAR2(128);
    v_names name_arr := name_arr();
    v_types type_arr := type_arr();
    v_sql   VARCHAR2(500);
BEGIN
    -- 1. 收集需刪除物件（排除 INDEX、LOB 由父物件級聯刪除）
    v_sql := q'[
        SELECT object_name, object_type
        FROM user_objects
        WHERE object_type NOT IN ('INDEX','LOB','LOB PARTITION','INDEX PARTITION')
        ORDER BY 
            CASE object_type 
                WHEN 'TRIGGER' THEN 1
                WHEN 'VIEW' THEN 2
                WHEN 'PROCEDURE' THEN 3
                WHEN 'FUNCTION' THEN 4
                WHEN 'PACKAGE' THEN 5
                WHEN 'TYPE' THEN 6
                WHEN 'SEQUENCE' THEN 7
                WHEN 'TABLE' THEN 8
                ELSE 9
            END
    ]';
    EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_names, v_types;

    -- 2. 依相依順序動態刪除
    FOR i IN v_names.FIRST .. v_names.LAST LOOP
        v_sql := 'DROP ' || v_types(i) || ' ' || v_names(i);
        BEGIN
            EXECUTE IMMEDIATE v_sql;
            DBMS_OUTPUT.PUT_LINE('DROPPED: ' || v_types(i) || ' ' || v_names(i));
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('FAILED: ' || v_types(i) || ' ' || v_names(i) 
                    || ' - ' || SQLERRM);
        END;
    END LOOP;
END;
/
-- 執行後再手動 PURGE RECYCLEBIN 徹底釋放空間

關鍵設計點解析

1. 排除 INDEX/LOB：隨 TABLE 級聯刪除，單獨 DROP 會報錯。
2. 刪除順序排序：TRIGGER/VIEW/PROC/FUNC/PACKAGE/TYPE/SEQUENCE → TABLE，避免相依錯誤。
3. EXCEPTION 繼續執行：單一物件失敗不中斷整體流程，失敗原因輸出日誌供事後處理。
4. BULK COLLECT：單次查詢取回所有物件，效能優於游標循環。

進階：處理外鍵依賴的強力版本

-- 先停用所有外鍵
FOR c IN (SELECT constraint_name, table_name
          FROM user_constraints
          WHERE constraint_type = 'R') LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' || c.table_name 
        || ' DISABLE CONSTRAINT ' || c.constraint_name;
END LOOP;
-- 執行上述刪除邏輯
-- 最後 PURGE RECYCLEBIN

方法對比與選擇建議

總結

首選 DROP USER CASCADE，標準、高效、無遺漏。保留用戶清空物件 時，使用帶有依賴排序、異常捕獲、日誌記錄的 PL/SQL 動態腳本。記得最後 PURGE RECYCLEBIN 或 PURGE DBA_RECYCLEBIN 徹底釋放空間。