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

Error:

SQL> drop user TEST_SCHEMA cascade;
drop user TEST_SCHEMA cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

Solution:

Step:1 Check the session details for this user by using below query

SQL> select 'alter system kill session '''||sid||','||serial#|| ''' immediate ;' from v$session where username='TEST_SCHEMA';

O/P:

SQL> select 'alter system kill session '''||sid||','||serial#|| ''' immediate ;' from v$session where username='TEST_SCHEMA';

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'

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

alter system kill session '13,32432' immediate ;

alter system kill session '15,50630' immediate ;

alter system kill session '18,13767' immediate ;

alter system kill session '19,37025' immediate ;

alter system kill session '23,25743' immediate ;

alter system kill session '386,41994' immediate ;

Step-2 Kill all the sessions by using below query;

alter system kill session '13,32432' immediate ;

alter system kill session '15,50630' immediate ;

alter system kill session '18,13767' immediate ;

alter system kill session '19,37025' immediate ;

alter system kill session '23,25743' immediate ;

alter system kill session '386,41994' immediate ;

Step:3 After kill the sessions,then we will drop the user

SQL> drop user TEST_SCHEMA cascade;

User dropped.