Oracle ORA-01940 無法刪除已連線使用者：秒級解決腳本與原理

錯誤重現

SQL> DROP USER a CASCADE;
DROP USER a CASCADE
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

根本原因

使用者 `a` 仍有作用中 Session（連線），Oracle 禁止刪除以防資料不一致。

標準解決三步驟

-- 1. 查詢該使用者的 Session
SELECT username, sid, serial#
FROM v$session
WHERE username = 'A';

-- 輸出範例：
-- USERNAME   SID  SERIAL#
-- ---------  ---  -------
-- A          9    4

-- 2. 殺掉 Session (SID, SERIAL#)
ALTER SYSTEM KILL SESSION '9,4';

-- 3. 再次刪除使用者
DROP USER a CASCADE;
-- User dropped.

自動化清理腳本 (適用批次刪除多使用者)

SET SERVEROUTPUT ON
DECLARE
  CURSOR c_sess IS
    SELECT sid, serial#
    FROM v$session
    WHERE username IN ('USER1', 'USER2', 'USER3');  -- 目標使用者清單
BEGIN
  FOR r IN c_sess LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || r.sid || ',' || r.serial# || ''' IMMEDIATE';
      DBMS_OUTPUT.PUT_LINE('Killed: ' || r.sid || ',' || r.serial#);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Fail kill ' || r.sid || ',' || r.serial# || ': ' || SQLERRM);
    END;
  END LOOP;
END;
/

-- 接著執行 DROP USER ... CASCADE

關鍵選項： `IMMEDIATE` 讓 Session 立即中斷交易並釋放鎖，避免等待交易回滾導致 `KILL SESSION` 指令卡住。

進階場景：RAC 環境

-- 查詢全叢集 Session
SELECT inst_id, sid, serial#, username
FROM gv$session
WHERE username = 'A';

-- 在對應 Instance 執行 KILL
ALTER SYSTEM KILL SESSION '9,4,@1';  -- @1 表示 Instance 1

常見誤區

預防措施

• 應用程式連線池設定 `idle_timeout`、`max_lifetime`，避免殭屍連線長期佔用。
• 部署前執行「優雅關機」腳本：停止應用服務 → 等待連線池排空 → 執行 DROP USER。
• 稽核 `DBA_USERS` 與 `V$SESSION` 定期比對，發現孤立使用者即時清理。

結語

ORA-01940 純屬「使用者仍有活躍 Session」的保護機制。掌握 `V$SESSION` 查詢 → `ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE` → `DROP USER ... CASCADE` 三步驟，並封裝為自動化腳本，即可在不重啟資料庫前提下安全、快速完成使用者清理。