not able to drop a user in oracle

Question

Hi,

I am trying to drop a tablespace in oracle 10g , using my application .

A bit about my application -- In my application I can create tablespaces.

Now what happens in oracle is that when you create a tablespace , then a new user automatically gets created and is attached to the database.

When you have to drop a tablespace what one has to do is to , first drop the user connected to the database and then the database.

When I try to drop a user associated with a tablespace. An exception is thrown by the database which is the System.Data.OracleClient.OracleException

The details of the exception are as follows - ORA - 01904 (Can Not drop a user that is currently connected)

The thing is I have closed all the connections.Pretty sure about this.

Still oracle is throwing this exception.

Any suggestions???

Still it is not able to drop the user and throws the exception.

Answer 1

It can happen that you closed applications but did not ended Oracle sessions for that user. Log in as sysdba and query active sessions:

SQL> select sid, serial#, username from v$session;

       SID    SERIAL# USERNAME                      
---------- ---------- ------------------------------
       122       2557 SYS                   
       126       7878 SOME_USER                      

If you find your user in this list then kill all his sessions:

SQL> alter system kill session 'sid,serial#';

Answer 2

Seems to be your error code is ORA-01940 and not ORA-01904 which says -

ORA-01940: cannot DROP a user that is currently logged in

Cause: An attempt was made to drop a user that was currently logged in.

Action: Make sure the user is logged out, then re-execute the command.

Hope the below link might help you -

http://www.dba-oracle.com/t_ora_01940_cannot_drop_user.htm

Answer 3

We do following and works..

ALTER TABLESPACE "OUR_INDEX" OFFLINE NORMAL;
DROP TABLESPACE "OUR_INDEX" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Answer 4

Please be sure that the user you're trying to drop is not currently connected. I did encounter this problem last year. My workaround was to restart the database. Once the database is up i drop the user.

Another workaround i haven't tried was to restart the listener. This too (logically) can ensure that the 'to be dropped' user is not connected when the listener is down.

This workaround (of course) cannot be used in production database.

Answer 5

A user does NOT automatically get created when you create a tablespace.

A user does get assigned a default tablespace. They may (or may not) create objects in that tablespace. They may (or may not) create objects in other tablespaces too.

Generally, rather than dropping the user, I would drop the user's objects. Then lock the account so they can't log in again. Then revoke any privileges they have.

If desired, you can then drop the 'unused' users after month or so.