Dropping connected users in Oracle database

Question

I want to drop some users in Oracle DB using sqlplus but I am getting error   SQL gt  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 gt  select sid serial  from v session where username    test    no rows selected   Please help me how to drop users in this case

User · Answer

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    amp  amp  return 1     schema  1 fi  sqlplus   as sysdba  lt  lt 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   gt  2    -- Prevent ORA-01940  cannot drop a user that is currently connected end    drop user  schema cascade  quit EOF

User · Answer

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

User · Answer

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 gt  select  alter system kill session        sid           serial         immediate   from v session where username    amp USERNAME   SQL gt  DROP USER barbie CASCADE

User · Answer

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  -- lt  lt  lt --   AND p addr      s paddr      Pass actual SID and SERIAL  values for user TEST then drop user      ALTER SYSTEM KILL SESSION   lt SID gt    lt SERIAL gt

User · Answer

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     lt your schema gt    - 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

User · Answer

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

User · Answer

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

User · Answer

Solution     login as sysdaba   sqlplus    as sysdba   then   sql gt Shutdown immediate   sql gt startup restrict   sql gt drop user TEST cascade    If you want to re-activate DB normally either reset the server or    sql gt Shutdown immediate   sql gt startup

User · Answer

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 gt Shutdown immediate   sql gt startup restrict   sql gt 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

User · Answer

This can be as simple as  SQL gt  ALTER SYSTEM ENABLE RESTRICTED SESSION   SQL gt  DROP USER test CASCADE   SQL gt  ALTER SYSTEM DISABLE RESTRICTED SESSION

User · Answer

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

User · Answer

Do a query   SELECT   FROM v session s   Find your user and do the next query  with appropriate parameters    ALTER SYSTEM KILL SESSION   lt SID gt    lt SERIAL gt

[oracle] Dropping connected users in Oracle database

Examples related to oracle