This discussion is archived
4 Replies Latest reply: Oct 3, 2013 6:49 AM by Vladimir Rodic RSS

Kill user sessions dynamically through PL/SQL

Vladimir Rodic Newbie
Currently Being Moderated

Hello,

 

Every night I have to DROP one user in order to recreate the entire schema. In order to do this I have to disconnect the user from the DB by killing its sessions. I am using following PL/SQL code

 

ALTER SYSTEM ENABLE RESTRICTED SESSION;

 

begin

    for x in (

            select Sid, Serial#, machine, program

            from v\$session

       where username = 'STARSTAGING_DB'

   ) loop

          execute immediate 'Alter System DISCONNECT Session '''|| x.Sid

                  || ',' || x.Serial# || ''' IMMEDIATE';

    end loop;

end;

/

 

DROP USER starstaging_db CASCADE;

 

ALTER SYSTEM DISABLE RESTRICTED SESSION;

 

I have also tried using keyword KILL instead of DISCONNECT of session.

 

Most of the day this works, user is dropped and new one is created, but sometimes I get the following problem.

The problem that has occurred in a log file is:

 

PL/SQL procedure successfully completed.

 

DROP USER starstaging_db CASCADE

*

ERROR at line 1:

ORA-01940: cannot drop a user that is currently connected

 

 

 

System altered.

 

CREATE USER STARSTAGING_DB IDENTIFIED BY *****

            *

ERROR at line 1:

ORA-01920: user name 'STARSTAGING_DB' conflicts with another user or role name

 

Does anyone have an idea how I can make sure that the user for sure disconnects, so that I can drop him?

 

Best Regards,

Vladimir

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points