Wednesday, October 30, 2013

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.

2 comments:

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

    ReplyDelete
    Replies
    1. Thank you for your comment , Restricted mode in database mean only users with administrative privileges can access it Please Refer to Oracle Documentation :
      http://docs.oracle.com/cd/B28359_01/server.111/b28310/start002.htm#ADMIN11155

      Delete