ORA-01940: cannot drop a user that is currently connected

While dropping a user in oracle database , you may face below error. ORA-01940

Problem:-

SQL> drop user bhom cascade;

drop user bhom cascade

*

ERROR at line 1:

ORA-01940: cannot drop a user that is currently connected

SQL>

Solution:-

Run the below by entering the schema name which you want to drop. As it will kill all the connected session for particular schema. 

BEGIN

  FOR r IN (select sid,serial# from v$session where username = 'BHOM')

  LOOP

    EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid 

      || ',' || r.serial# || '''';

  END LOOP;

END;

/

Once the above query gets completed you can try to drop the schema again.

SQL> BEGIN

  FOR r IN (select sid,serial# from v$session where username = 'BHOM')

  LOOP

    EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid

      || ',' || r.serial# || '''';

  END LOOP;

END;

  /

PL/SQL procedure successfully completed.

SQL> drop user bhom cascade;

User dropped.

SQL>