Pages

Tuesday, April 14, 2015

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

SQL Error: ORA-01940: cannot drop a user that is currently connected
01940. 00000 -  "cannot drop a user that is currently connected"
*Cause:    Attempt was made to drop a user that is currently logged in.
*Action:   Make sure user is logged off, then repeat command.


Issue: When I am trying to drop a user from Oracle database, I am getting the above error though my application is in stopped stated which uses this schema. 
 Basically I am not a DBA to find out SID and SERIAL number and issue command to kill the session.

Solution: Use "Oracle SQL Developer" if you already having it. It is very simple and quick process. I will demonstrate how to achieve this. Just follow below steps.

 We can kill user session with SQL Developer option.


Step 1: Login to SQL Developer with system user account or any other user who are having DBA privileges and try dropping a user.


Step 2: Go to Tools -> Monitor Sessions.

SQL Developer Tools Monitor Sessions

Step 3: Select your database connection from the drop down list.

Select your database connection

Step 4:It displays all user session which are active.

List user sssions

 Step 5: Select the user session you want to kill and click "Apply" button to kill the session.


Kill session prompt

 Step 6: You will get the below confirmation message.

 
 This demonstration is intended only for testing or demo environments who are learning the technology and not for advanced users and DBA.

No comments:

Post a Comment