User Not Able To Drop Even When I killed the session.

Today while i was working on Database trying to Drop user called PRD_MDS the normal error appearing to me :

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

Which is very normal error and the first thing you are trying to check is v$session

SQL> select sid,serial# ,username from v$session where username like '%PRD%';
---------- ---------- ------------------------------
       745        821 PRD_MDS
SQL> alter system kill session '745,821' immediate ;
System altered.

After this i should be able to drop this user without any problem BUT !!!!

SQL> drop user PRD_MDS ;
drop user PRD_MDS
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
I tried to figure out what is going on , Even i tried alter system kill session 10 times the above error will appear BUT there's always solutions :

SQL> startup force restrict;
ORACLE instance started.
Total System Global Area 7686086656 bytes
Fixed Size                  2228032 bytes
Variable Size            1811939520 bytes
Database Buffers         5729419264 bytes
Redo Buffers              142499840 bytes
Database mounted.
Database opened.

 SQL> drop user PRD_MDS cascade ;
User dropped.


  1. please give the explanation why we have to start database in restricted mode.

    1. Thank you for your comment , Restricted mode in database mean only users with administrative privileges can access it Please Refer to Oracle Documentation :


Post a Comment

Popular posts from this blog

Connection refused; No available router to destination On Weblogic Node Manager Log

How to change SGA in Oracle Rac