User not deleted in Oracle, ORA-01940 prompt "Unable to delete current connected user"

Oracle Delete user prompt Unable to delete the currently connected user two workarounds are as follows:

1, first lock the user, and then query the process number, and finally delete the corresponding process, delete the corresponding user

Sql>alter user XXX account lock;

Sql>select * from v$session WHERE username= ' lgdb ';

Sql>alter system kill session ' XX,XX '

Sql>drop user XX Cascade

2.shutdown Drop the database and restart

The specific query process number, and finally delete the corresponding process steps are as follows

1) View the user's connection status

Select username,sid,serial# from V$session

The following results:

Username Sid serial#

Netbnew 513 22974

Netbnew 514 18183

Netbnew 516 21573

Netbnew 531 9

TS 532 4562

(2) Find the SID to delete the user, and serial, and remove

Such as: you want to delete the user ' ts ', you can do this:

Alter system kill session ' 532,4562 '

(3) Delete user

Drop USER TS Cascade

(* *) If after the drop also prompts ORA-01940: Unable to delete the currently linked user, indicating that there is a connection session, you can check the status of the session to determine whether the session was killed, the following statement to see:

Select Saddr,sid,serial#,paddr,username,status from v$session where username are not null

The results are as follows (for example in my library):

SADDR SID serial# paddr username status

564a1e28 513 22974 569638f4 netbnew ACTIVE

564A30DC 514 18183 569688CC netbnew INACTIVE

564a5644 516 21573 56963340 netbnew INACTIVE

564B6ED0 531 9 56962d8c netbnew INACTIVE

564b8184 532 4562 56a1075c WUZHQ killed

Status is to delete the user's session state, if it is inactive, the description has not been killed, if the status is killed, the description has been kill.

This shows that TS this user's session has been killed. Users can be safely removed at this time.

User not deleted in Oracle, ORA-01940 prompt "Unable to delete current connected user"