Comprehensive Solution for Forcefully Dropping Connected Users in Oracle Database

Problem Background and Error Analysis

In Oracle database administration practice, dropping user accounts is a common maintenance operation. However, when the target user currently has active connections, executing the DROP USER command triggers ORA-01940 error: "cannot drop a user that is currently connected." This protection mechanism prevents data inconsistency and operational disruption but also creates operational challenges for administrators.

Core Solution: Query and Terminate Active Sessions

To successfully drop a connected user, it is essential to first identify and terminate all active sessions. Oracle's data dictionary view v$session stores all current session information, but a crucial detail must be noted: usernames are stored in uppercase in this view. This is a key point often overlooked by administrators.

The following query demonstrates how to correctly retrieve active session information for target users:

SELECT s.sid, s.serial#, s.status, p.spid 
  FROM v$session s, v$process p 
 WHERE s.username = 'TEST' 
   AND p.addr(+) = s.paddr

Key improvements in this code include:

1. Explicitly specifying username in uppercase 'TEST' to ensure accurate matching
2. Using outer join to associate v$process view, obtaining operating system process IDs
3. Returning critical information including session ID, serial number, status, and process ID

Session Termination Operation

After obtaining valid SID and SERIAL# values, specific sessions can be terminated using:

ALTER SYSTEM KILL SESSION '<SID>, <SERIAL>'

In practical operation, replace <SID> and <SERIAL> with actual values from the query. For example, if query results show SID=123 and SERIAL#=456, the command should be:

ALTER SYSTEM KILL SESSION '123, 456'

Alternative Approach Analysis

Beyond the standard method, a more aggressive solution exists: dropping users through database restart. This approach involves the following steps:

-- Log in as sysdba
sqlplus / as sysdba

-- Shutdown database immediately
SHUTDOWN IMMEDIATE;

-- Start in restricted mode
STARTUP RESTRICT;

-- Drop user
DROP USER TEST CASCADE;

-- Return to normal operation (optional)
SHUTDOWN IMMEDIATE;
STARTUP;

While effective, this method has significant drawbacks:

• Requires complete database restart, affecting all users and services
• In restricted mode, only users with RESTRICTED SESSION privilege can connect
• Not suitable for production environments requiring high availability

Best Practice Recommendations

Based on comparative analysis of both methods, we recommend the following operational workflow:

1. Session Query Phase: Always query v$session view using uppercase usernames to ensure query accuracy
2. Session Termination Phase: Prioritize ALTER SYSTEM KILL SESSION command to target specific sessions
3. User Drop Phase: Execute DROP USER ... CASCADE command after confirming all sessions are terminated
4. Exception Handling: Consider database restart approach only if standard methods fail, assessing business impact

Technical Key Points Summary

The technical solutions discussed involve multiple core Oracle database concepts:

• Data Dictionary Views: v$session and v$process views provide real-time session and process information
• Session Management: Oracle uniquely identifies each session through SID and SERIAL#, forming the foundation of session control
• Privilege Control: ALTER SYSTEM command requires appropriate system privileges, typically executed by DBAs
• Transaction Integrity: Forceful session termination may cause uncommitted transactions to roll back, requiring business-level impact assessment

By understanding these underlying mechanisms, database administrators can perform user management operations more safely and efficiently, ensuring stable operation of database systems.