ORA-01940 Cannot DROP a User That Is Currently Connected

ORA-01940: Cannot DROP a User That Is Currently Connected

Error Overview

Error Text: ORA-01940: cannot DROP a user that is currently connected

The ORA-01940 error occurs when attempting to drop a database user while that user has one or more active sessions connected to the database. Oracle prevents dropping users with active connections to maintain data integrity and prevent orphaned transactions.

Common Causes

1. Active User Sessions

• Users currently logged in and working
• Application connection pools with persistent connections
• Scheduled jobs running under the user account

2. Background Processes

• Long-running batch jobs
• Scheduled DBMS_SCHEDULER jobs
• Database links being used by other sessions

3. Locked Sessions

• Sessions in transaction with uncommitted changes
• Sessions waiting on locks
• Idle sessions from connection pooling

4. System/Application Connections

• Application servers maintaining connection pools
• Reporting tools with open connections
• ETL processes with persistent connections

Diagnostic Queries

Find All Sessions for User

-- List all sessions for the user
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.program,
  s.machine,
  s.logon_time,
  s.last_call_et/60 as idle_minutes,
  s.sql_id
FROM v$session s
WHERE s.username = UPPER('&username')
ORDER BY s.logon_time;

-- Include OS process information
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.program,
  s.machine,
  p.spid as os_pid,
  s.logon_time
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.username = UPPER('&username');

Check for Active Transactions

-- Sessions with active transactions
SELECT
  s.sid,
  s.serial#,
  s.username,
  t.start_time,
  t.status as txn_status,
  t.used_ublk as undo_blocks,
  t.used_urec as undo_records
FROM v$session s
JOIN v$transaction t ON s.taddr = t.addr
WHERE s.username = UPPER('&username');

-- Check for locks held by user
SELECT
  s.sid,
  s.serial#,
  l.type,
  l.lmode,
  l.request,
  o.object_name
FROM v$lock l
JOIN v$session s ON l.sid = s.sid
LEFT JOIN dba_objects o ON l.id1 = o.object_id
WHERE s.username = UPPER('&username')
  AND l.type != 'AE';

Check for Jobs Running as User

-- DBMS_SCHEDULER jobs
SELECT
  job_name,
  state,
  run_count,
  last_start_date,
  next_run_date
FROM dba_scheduler_jobs
WHERE owner = UPPER('&username');

-- Currently running jobs
SELECT
  job_name,
  session_id,
  running_instance,
  elapsed_time,
  cpu_used
FROM dba_scheduler_running_jobs
WHERE owner = UPPER('&username');

-- Legacy DBMS_JOB
SELECT
  job,
  what,
  last_date,
  next_date,
  broken
FROM dba_jobs
WHERE schema_user = UPPER('&username');

Resolution Steps

1. Graceful Session Termination

-- Generate kill statements for review
SELECT
  'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' as kill_cmd
FROM v$session
WHERE username = UPPER('&username');

-- Kill specific session
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- Example: Kill session 125 with serial 45632
ALTER SYSTEM KILL SESSION '125,45632' IMMEDIATE;

2. Kill All User Sessions

-- PL/SQL block to kill all sessions for a user
DECLARE
  v_username VARCHAR2(30) := 'TARGET_USER';
BEGIN
  FOR rec IN (
    SELECT sid, serial#
    FROM v$session
    WHERE username = v_username
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE
        'ALTER SYSTEM KILL SESSION ''' || rec.sid || ',' || rec.serial# || ''' IMMEDIATE';
      DBMS_OUTPUT.PUT_LINE('Killed: ' || rec.sid || ',' || rec.serial#);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Failed to kill: ' || rec.sid || ',' || rec.serial# || ' - ' || SQLERRM);
    END;
  END LOOP;
END;
/

3. Disconnect Sessions (RAC-Aware)

-- For RAC environments, use DISCONNECT SESSION
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

-- Immediate disconnect
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

-- Kill session on specific instance
ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id' IMMEDIATE;

4. Stop Jobs Before Dropping User

-- Disable all scheduler jobs for user
BEGIN
  FOR rec IN (
    SELECT job_name
    FROM dba_scheduler_jobs
    WHERE owner = UPPER('&username')
  ) LOOP
    DBMS_SCHEDULER.DISABLE(UPPER('&username') || '.' || rec.job_name);
  END LOOP;
END;
/

-- Stop running jobs
BEGIN
  FOR rec IN (
    SELECT job_name
    FROM dba_scheduler_running_jobs
    WHERE owner = UPPER('&username')
  ) LOOP
    DBMS_SCHEDULER.STOP_JOB(UPPER('&username') || '.' || rec.job_name, force => TRUE);
  END LOOP;
END;
/

-- Mark legacy jobs as broken
UPDATE dba_jobs
SET broken = 'Y'
WHERE schema_user = UPPER('&username');
COMMIT;

5. Drop User After Sessions Cleared

-- Verify no sessions remain
SELECT COUNT(*) as remaining_sessions
FROM v$session
WHERE username = UPPER('&username');

-- Drop user with all objects
DROP USER username CASCADE;

Prevention Strategies

1. Pre-Drop Checklist Script

-- Complete pre-drop analysis
CREATE OR REPLACE PROCEDURE prepare_user_drop(p_username VARCHAR2) AS
  v_count NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('=== Pre-Drop Analysis for ' || p_username || ' ===');

  -- Check sessions
  SELECT COUNT(*) INTO v_count FROM v$session WHERE username = UPPER(p_username);
  DBMS_OUTPUT.PUT_LINE('Active Sessions: ' || v_count);

  -- Check objects
  SELECT COUNT(*) INTO v_count FROM dba_objects WHERE owner = UPPER(p_username);
  DBMS_OUTPUT.PUT_LINE('Owned Objects: ' || v_count);

  -- Check scheduler jobs
  SELECT COUNT(*) INTO v_count FROM dba_scheduler_jobs WHERE owner = UPPER(p_username);
  DBMS_OUTPUT.PUT_LINE('Scheduler Jobs: ' || v_count);

  -- Check granted privileges
  SELECT COUNT(*) INTO v_count
  FROM dba_tab_privs
  WHERE grantee = UPPER(p_username) OR grantor = UPPER(p_username);
  DBMS_OUTPUT.PUT_LINE('Table Privileges: ' || v_count);

  -- Check dependencies
  SELECT COUNT(*) INTO v_count
  FROM dba_dependencies
  WHERE referenced_owner = UPPER(p_username);
  DBMS_OUTPUT.PUT_LINE('Dependencies on User Objects: ' || v_count);
END;
/

2. Automated User Removal Script

-- Complete user removal procedure
CREATE OR REPLACE PROCEDURE safe_drop_user(
  p_username VARCHAR2,
  p_force BOOLEAN DEFAULT FALSE
) AS
  v_session_count NUMBER;
BEGIN
  -- Kill all sessions
  FOR rec IN (
    SELECT sid, serial#
    FROM v$session
    WHERE username = UPPER(p_username)
  ) LOOP
    EXECUTE IMMEDIATE
      'ALTER SYSTEM KILL SESSION ''' || rec.sid || ',' || rec.serial# || ''' IMMEDIATE';
  END LOOP;

  -- Wait and verify
  DBMS_LOCK.SLEEP(5);

  SELECT COUNT(*) INTO v_session_count
  FROM v$session
  WHERE username = UPPER(p_username);

  IF v_session_count > 0 AND NOT p_force THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Still ' || v_session_count || ' sessions connected');
  END IF;

  -- Drop user
  EXECUTE IMMEDIATE 'DROP USER ' || p_username || ' CASCADE';
  DBMS_OUTPUT.PUT_LINE('User ' || p_username || ' dropped successfully');
END;
/

3. Schedule User Removal

-- Create job to drop user during maintenance window
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name   => 'DROP_OLD_USER_JOB',
    job_type   => 'PLSQL_BLOCK',
    job_action => 'BEGIN safe_drop_user(''OLD_USER'', TRUE); END;',
    start_date => TO_TIMESTAMP('2024-01-15 02:00:00', 'YYYY-MM-DD HH24:MI:SS'),
    enabled    => TRUE,
    comments   => 'Scheduled removal of OLD_USER'
  );
END;
/

Related Errors

• ORA-01918 - User does not exist
• ORA-01031 - Insufficient privileges
• ORA-00028 - Your session has been killed
• ORA-01017 - Invalid username/password

Emergency Response

Quick Session Kill and Drop

-- One-liner to kill all and drop (use with caution)
BEGIN
  FOR r IN (SELECT sid, serial# FROM v$session WHERE username = 'TARGET_USER') LOOP
    EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || r.sid || ',' || r.serial# || ''' IMMEDIATE';
  END LOOP;
  DBMS_LOCK.SLEEP(3);
  EXECUTE IMMEDIATE 'DROP USER TARGET_USER CASCADE';
END;
/

Monitor Session Termination

-- Watch sessions being terminated
SELECT
  sid, serial#, status,
  event, seconds_in_wait
FROM v$session
WHERE username = UPPER('&username');
-- Run repeatedly until count = 0