刪除一個使用者遇到的問題和解決

記錄一次刪除使用者問題解決。

問題出現

這幾天在進行資料庫物件重建的工作，需要將資料庫使用者user刪除重新建立。在確保開發團隊下班離開之後，刪除使用者失敗。

使用SQL命令列和EM進行刪除，都報錯。

SQL> drop user nbs;

drop user nbs

*

第1行出現錯誤:

ORA-01940:無法刪除當前連線的使用者

反覆幾次都報同樣的錯誤。

問題分析

從錯誤提示上看，表示當前Oracle在嘗試進行刪除的時候，發現有使用者nbs的session連線連入資料庫。Oracle在刪除user的時候，如果有當前使用者連線，是不允許刪除的。

此時所有的開發人員均已離開，不存在遠端連線資料庫的可能。

這樣，如何確定是哪臺機器還在連入呢？利用v$session檢視。

SQL> select sid, serial#, username, machine, TERMINAL, program from v$session where username='NBS';

 SID SERIAL# USERNAME    MACHINE  TERMINAL        PROGRAM

---- ------- -------------- ------------------------- -------------   -------------

 29 148 NBS WORKGROUP\WWW-0E6111DFF74 WWW-0E6111DFF74 plsqldev.exe

按照NBS使用者搜尋，果然發現連線使用者資訊。是位於WORKGROUP\ WWW-0E6111DFF74的計算機使用者，開啟了一個plsqldev.exe（PL/SQL Developer）程式。程式始終維持了一個連線在資料庫。

猜測可能是開發人員沒有及時關閉開發工具，使連線保持。

解決

瞭解原因，可以使用kill命令，將會話強制終止掉。

//kill session命令後兩位引數分別為sid和Serial#，可以在v$session中找出；

SQL> alter system kill session '29,148';

System altered

這時，可以看到一個現象。如果我們再進行查詢v$session，發現該會話資訊還存在。

SQL> select sid, serial#, username, status from v$session where username='NBS';

      SID   SERIAL# USERNAME                      STATUS

---------- ---------- ------------------------------ --------

       29       148 NBS                           KILLED

該條記錄還存在，該session還存在。但是，status狀態已經標誌為KILLED，之前為ACTIVE和INACTIVE狀態。 這個時候，再進行刪除。

SQL> drop user nbs;

User dropped

刪除成功。但注意：此時session資訊還存在，即使是killed。

SQL> select sid, serial#, username, status from v$session where username='NBS';

      SID   SERIAL# USERNAME                      STATUS

---------- ---------- ------------------------------ --------

       29       148 NBS                           KILLED

說明：此時被kill掉的會話資源，並沒有及時被例項後臺程式PMON回收。那麼，如果這個時候，被kill掉的會話要求通訊怎麼辦。

下面是實驗環境。

SQL> select count(*) from user_tables;

ORA-01017: invalid username/password: login denied;

結果是報錯，說明連線已經斷開，Process被迫中止。回頭再看v$session.

SQL> select sid, serial#, username, status from v$session where username='NBS';

      SID   SERIAL# USERNAME                      STATUS

---------- ---------- ------------------------------ --------

///會話資訊被清理！！

這個小小的問題解決，告訴我們幾個資訊：

1、當使用者存在連線資料庫的會話是，Oracle是不允許對使用者進行刪除的；

2、使用alter system kill session進行會話清理時，只是對會話打上killed的標記，沒有對客戶端連入的Server Process做記憶體回收工作；更進一步實驗，killed的過程，是將session的paddr地址指向了一個虛擬地址；

3、Kill之後，process與session的連線實際上已經被打斷；

4、回收被kill的會話、Server Process資源，是由後臺程式PMON完成，但是預設情況下，是需要等待一定時間的；

5、被kill掉之後，雖然資源沒有回收，但是刪除使用者的操作可以完成了。可見Oracle對會話的疑慮可能在於事務和資料完整性；

6、被kill掉的會話，如果再次嘗試連線，自身會受到報錯；

7、再次連線的時候，會啟動PMON，進行資源回收清理。至少將嘗試連線的被kill會話資源回收；

PS：第二天聯絡開發人員，發現是因為關機錯誤引起一直沒有關閉plsql developer。看來問題促進學習確實有效果！