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%’;
       SID    SERIAL# USERNAME
———- ———- ——————————
       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.

ORA-12516: TNS:listener could not find available handler

after the increasing the Number processes in the Oracle database, it solved the problem.

But Before this , i am gonna show you some steps to check the process via OS and Database LETS BEGIN :

  • check if the number of connections reaches the database’s  process parameter using following Unix command:
ps -ef | grep oracleSID | grep -v grep | wc -l
or
ps aux | grep oracleSID | grep -v grep | wc -l
  •  check if the number of connections reaches the database’s  process parameter using following Database Command :
SQL> show parameter processes

NAME TYPE VALUE
------------------ ----------- -------
processes integer 150

SQL> select count(*) from v$process;

COUNT(*)
----------
149
SQL> show parameter sessions

NAME TYPE VALUE
------------------ ----------- -------
Session integer 150

SQL> select count(*) from v$session;

COUNT(*)
----------
149

Now We need To Increase the Both Parameter By :

SQL> alter system set processes=300 scope=spfile;

System altered.
 
SQL> alter system set Session=300 scope=spfile;

System altered.

If this Solution Not Work For you , Try this One :

SQL> alter system set local_listener=
“(ADDRESS=(PROTOCOL=tcp)(HOST=10.122.28.12)(PORT=1521))” sid=’ORCL1′;

System altered.

SQL> show parameter local

NAME                                 TYPE        VALUE
———————————— ———– ——————————
local_listener                       string      (ADDRESS=(PROTOCOL=tcp)(HOST=1
                                                 0.122.28.12)(PORT=1521))

Thank you 

Osama Mustafa