4 Replies Latest reply: Oct 3, 2013 8:49 AM by Vladimir Rodic RSS

    Kill user sessions dynamically through PL/SQL

    Vladimir Rodic

      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