Dropping connected users in Oracle database

I want to drop some users in Oracle DB using sqlplus but I am getting error:

SQL> DROP USER test CASCADE; DROP USER test CASCADE * ERROR at line 1: ORA-01940: cannot drop a user that is currently connected 

I followed the link in SO to find out the sessions - Dropping a connected user from an Oracle 10g database schema

But when I ran the command I am not getting any results:

SQL> select sid,serial# from v$session where username = 'test'; no rows selected 

Please help me how to drop users in this case.

13 Answers

Users are all capitals in v$session (and data dictionary views). If you match with capitals you should find your session to kill.

SELECT s.sid, s.serial#, s.status, p.spid FROM v$session s, v$process p WHERE s.username = 'TEST' --<<<-- AND p.addr(+) = s.paddr / 

Pass actual SID and SERIAL# values for user TEST then drop user...:

ALTER SYSTEM KILL SESSION '<SID>, <SERIAL>' / 

Solution :

login as sysdaba:

sqlplus / as sysdba 

then:

sql>Shutdown immediate; sql>startup restrict; sql>drop user TEST cascade; 

If you want to re-activate DB normally either reset the server or :

sql>Shutdown immediate; sql>startup; 

:)

Issue has been fixed using below procedure :

DECLARE v_user_exists NUMBER; user_name CONSTANT varchar2(20) := 'SCOTT'; BEGIN LOOP FOR c IN (SELECT s.sid, s.serial# FROM v$session s WHERE upper(s.username) = user_name) LOOP EXECUTE IMMEDIATE 'alter system kill session ''' || c.sid || ',' || c.serial# || ''' IMMEDIATE'; END LOOP; BEGIN EXECUTE IMMEDIATE 'drop user ' || user_name || ' cascade'; EXCEPTION WHEN OTHERS THEN IF (SQLCODE = -1940) THEN NULL; ELSE RAISE; END IF; END; BEGIN SELECT COUNT(*) INTO v_user_exists FROM dba_users WHERE username = user_name; EXIT WHEN v_user_exists = 0; END; END LOOP; END; / 

Do a query:

SELECT * FROM v$session s;

Find your user and do the next query (with appropriate parameters):

ALTER SYSTEM KILL SESSION '<SID>, <SERIAL>';

If you use RAC then you need to use GV$* views instead V$*. Try to find your session by

select * from gv$session where username = 'test'; 

and then you can kill the session by

alter system kill session 'sid, serial#, @inst_id' immediate; 

This can be as simple as:

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; SQL> DROP USER test CASCADE; SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION; 

go to services in administrative tools and select oracleserviceSID and restart it

I was trying to follow the flow described here - but haven't luck to completely kill the session.. Then I fond additional step here:

What I did:
1. select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username = '<your_schema>'; - as described below.
Out put will be something like this:
alter system kill session '22,15' immediate;
2. alter system disconnect session '22,15' IMMEDIATE ; - 22-sid, 15-serial - repeat the command for each returned session from previous command
3. Repeat steps 1-2 while select... not return an empty table
4. Call drop user...

What was missed - call alter system disconnect session '22,15' IMMEDIATE ; for each of session returned by select 'alter system kill session '..

Sometimes Oracle drop user takes long time to execute. In that case user might be connected to the database. Better you can kill user session and drop the user.

SQL> select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' from v$session where username ='&USERNAME';

SQL> DROP USER barbie CASCADE;

I had the same problem, Oracle config in default affects letter register. In exact my Scheme_Name was written all Capital letters. You can see your Scheme_Name on "Other Users" tab, if you are using Oracle S

Basically I believe that killing all sessions should be the solution, but...

I found similar discussion - to my problem and that was I had no sessions for that users, but I still received the error. I tried also second the best answer:

sql>Shutdown immediate; sql>startup restrict; sql>drop user TEST cascade; 

What worked for me at the end was to login as the user, drop all tables manually - select for creating drop statements is

select 'drop table ' || TABLE_NAME || ';' from user_tables; 

(Needs to be re-run several times because of references)

I have no idea how is that related, I dropped also functions and sequences (because that was all I had in schema)

When I did that and I logged off, I had several sessions in v$session table and when I killed those I was able to drop user.

My DB was still started in restricted mode (not sure if important or not).

Might help someone else.

BTW: my Oracle version is Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

//'SYS' is username from where you wanted to kill session' SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'SYS'; **Step 1:** CREATE OR REPLACE PROCEDURE sys.kill_session(p_sid NUMBER, p_serial NUMBER) AS v_user VARCHAR2(30); BEGIN SELECT MAX(username) INTO v_user FROM v$session WHERE sid = p_sid AND serial# = p_serial; **Step 2** create or replace procedure kill_session( p_sid in number, p_serial# in number) is v_count pls_integer; BEGIN select count(*) into v_count from V$session where username = 'SYS' and sid = p_sid and serial# = p_serial# ; if ( v_count = 1 ) then execute immediate ' alter system kill session ''' || to_char(p_sid,'999999')||','|| to_char(p_serial#,'999999')||''''; else raise_application_error( -20001, 'You do not own session ''' || p_sid || ',' || p_serial# || '''' ); end if; END; / **Step 3** grant execute on kill_session to SYS; **Step 4** select inst_id, sid, serial#, username, action, program, service_name, con_id from gv$session where username like 'FCM_469'; Check there will be no sessions now **Step 5** DROP USER USER_345 CASCADE; Output:User Dropped 

Here's how I "automate" Dropping connected users in Oracle database:

# A shell script to Drop a Database Schema, forcing off any Connected Sessions (for example, before an Import) # Warning! With great power comes great responsibility. # It is often advisable to take an Export before Dropping a Schema if [ "$1" = "" ] then echo "Which Schema?" read schema else echo "Are you sure? (y/n)" read reply [ ! $reply = y ] && return 1 schema=$1 fi sqlplus / as sysdba <<EOF set echo on alter user $schema account lock; -- Exterminate all sessions! begin for x in ( select sid, serial# from v\$session where username=upper('$schema') ) loop execute immediate ( 'alter system kill session '''|| x.Sid || ',' || x.Serial# || ''' immediate' ); end loop; dbms_lock.sleep( seconds => 2 ); -- Prevent ORA-01940: cannot drop a user that is currently connected end; / drop user $schema cascade; quit EOF