Cannot drop a user that is currently connected - How to drop it

The reason I have this post here in the Hyperion blog is because of the close connection between the EPM environment and the DBA world - coz at times you have to act as an EPM admin as well as a database admin.

Say for example you want to drop/re-create an Oracle user.

Basically the query that you would write would be something like this:

DROP USER NIRMAL CASCADE;

The above syntax does the job of dropping the user "NIRMAL," however, if the user is connected somehow, you will get the error message "cannot drop a user that is currrently connected....."

It implies that one or more session is open for the user, and we need to kill the session before we can drop the user.

The first step is finding the session -

SYNTAX

SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME = 'NIRMAL'

The output will have the SID and SERIAL#s listed.

Now that we know the SID and SERIAL#s

SYNTAX

alter system kill session '34,4177';  ... etc(whatever the SID/SERIAL# combination)

The above SQL kills the session and disconnects the user.

Now all we need too do is run the DROP USER query again.

DROP USER NIRMAL CASCADE;

ALTERNATE WAY

SYNTAX

SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''';' FROM V$SESSION WHERE USERNAME = 'NIRMAL';

returns

one KILL statement per session for user 'NIRMAL'

Run the KILL statement and DROP the user.