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.
This question is related to
oracle
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;
I was trying to follow the flow described here - but haven't luck to completely kill the session.. Then I fond additional step here:
http://wyding.blogspot.com/2013/08/solution-for-ora-01940-cannot-drop-user.html
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 '..
Basically I believe that killing all sessions should be the solution, but...
I found similar discussion - https://community.oracle.com/thread/1054062 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
go to services in administrative tools and select oracleserviceSID and restart it
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
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
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>';
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;
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;
/
This can be as simple as:
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> DROP USER test CASCADE;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
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;
:)
Source: Stackoverflow.com