Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to be able to kill active sessions by a given user, and then drop that user, and do so within a single database call. Here is what I have so far:

DECLARE
cursor c is select *
                 from v$session 
                where username = 'USER_9FFB9';
c_row c%ROWTYPE;
begin
    open C;
    loop
FETCH c INTO c_row;
EXIT WHEN c%NOTFOUND;
        execute immediate 'alter system kill session ''' || 
                 c_row.sid || ',' || c_row.serial# || '''';
    execute immediate 'drop user user_9FFB9 cascade';
    end loop;
end;
/

The problem with this is that I'm getting back this error when I add the drop user call right after the kill session call:

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

However, if I execute the drop user statement after waiting only a couple of seconds in a separate statement, it works fine. What do I need to do to be able to kill the sessions and drop the user in one go? Is there a loop on v$session that I should do, checking to see if it's still open?

Update I've updated my script to this:

DECLARE
cursor c is select *
                 from v$session 
                where username = 'USER_abc';
c_row c%ROWTYPE;
begin
    open C;
    loop
FETCH c INTO c_row;
EXIT WHEN c%NOTFOUND;
        execute immediate 'alter user USER_abc ACCOUNT LOCK';

        execute immediate 'alter system kill session ''' ||
                 c_row.sid || ',' || c_row.serial# || '''';


    end loop;
    DBMS_LOCK.SLEEP(5);
end;

/

drop user user_abc cascade

I'm still getting this error, however:

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

Is there a commit or something like that which I need to add in there?

COMPLETED SCRIPT

With Justin Cave's help, here is the final script I have which works great:

DECLARE
  l_cnt integer;
BEGIN
  EXECUTE IMMEDIATE 'alter user user_#databaseName# account lock';
  FOR x IN (SELECT *
              FROM v$session
             WHERE username = 'USER_#databaseName#')
  LOOP
    EXECUTE IMMEDIATE 'alter system disconnect session ''' || x.sid || ',' || x.serial# || ''' IMMEDIATE';
  END LOOP;

  -- Wait for as long as it takes for all the sessions to go away
  LOOP
    SELECT COUNT(*)
      INTO l_cnt
      FROM v$session
     WHERE username = 'USER_#databaseName#';
    EXIT WHEN l_cnt = 0;
    dbms_lock.sleep( 2 );
  END LOOP;

  EXECUTE IMMEDIATE 'drop user user_#databaseName# cascade';
END;

Obviously #databaseName# is a placeholder for a simple value like ABC. Thanks Justin!

share|improve this question
add comment

3 Answers 3

up vote 8 down vote accepted

When you issue an ALTER SYSTEM KILL SESSION, Oracle merely marks the session as killed and does the actual work of killing the session asynchronously. That may take just a couple seconds, it may take many hours if the session has an uncommitted transaction that did a lot of work that now has to be rolled back or if the session needs to stay around in a killed state in order to notify the client that their session was terminated. It may, therefore, require a substantial amount of waiting before a user could be dropped.

You could do something like this where your loop will wait indefinitely for all the sessions to disappear.

DECLARE
  l_cnt integer;
BEGIN
  EXECUTE IMMEDIATE 'alter user abc_user account lock';
  FOR x IN (SELECT *
              FROM v$session
             WHERE username = 'ABC_USER')
  LOOP
    EXECUTE IMMEDIATE 'alter system kill session ''' || x.sid || ',' || x.serial# || '''';
  END LOOP;

  -- Wait for as long as it takes for all the sessions to go away
  LOOP
    SELECT COUNT(*)
      INTO l_cnt
      FROM v$session
     WHERE username = 'ABC_USER';
    EXIT WHEN l_cnt = 0;
    dbms_lock.sleep( 10 );
  END LOOP;

  EXECUTE IMMEDIATE 'drop user abc_user cascade';
END;

Generally, though, I'd question the problem you're trying to solve. I can't think of many times that it would make sense to drop a user that has active sessions at the time you decide to issue the drop. In the vast majority of cases, the presence of active sessions strongly implies that the account should not be dropped.

share|improve this answer
    
This works! Thanks, it is what I was looking for. Although, one thing still baffles me - why does it take SO LONG for the session to drop? I don't have any huge transactions pending, there is nothing at all happening, and yet it still takes upwards of 20-30 seconds before it finally dies and I can perform the drop. Ideas? If there is some system-wide setting I can adjust to fix this, that would be great too. –  Jake Feasel Feb 8 '12 at 19:09
1  
BTW Justin - you're right, this is a strange problem to need to solve. You helped me answer another "strange" Oracle question on SO yesterday, so you may be wondering just what I'm up to. Well, I've built a site - sqlfiddle.com - that allows anyone to define their own databases and write their own queries, and then share that with others. One of the options for the backend is Oracle. You might even find this site useful for answering StackOverflow questions. –  Jake Feasel Feb 8 '12 at 19:21
    
@JakeFeasel - 20 or 30 seconds isn't particularly long to wait between a kill request and a session actually being killed. There is some time waiting for PMON to wake up and look for stuff to do. There is some time to roll back the transaction, release the locks, and do other bits of clean-up. And then time the notification to the client that the session is gone happens the next time the client sends a request. Half a minute is pretty quick for all that. You might be able to get PMON to wake up more frequently to reduce some of that time but I'd really hate to suggest doing that. –  Justin Cave Feb 8 '12 at 19:32
2  
@JakeFeasel - I don't off the top of my head know how you'd get PMON to wake up more frequently, though I expect there is an underscore parameter (officially undocumented) for that. I have no idea what the ramifications of that change might be, though, particularly on XE. And you'd only likely be saving a few seconds by making it. You could potentially also look at doing an ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE but doing that frequently would scare me when it comes to system stability. –  Justin Cave Feb 8 '12 at 19:46
1  
Thanks again Justin! DISCONNECT ... IMMEDIATE appears to work great! See my updated question text with the final script. –  Jake Feasel Feb 8 '12 at 20:08
show 1 more comment

Your drop user is inside the loop. :-) Dig it out of there first.

You could do a ALTER USER xxxxx ACCOUNT LOCK first, so the user can not log in any more.

Then, insert a DBMS_LOCK.SLEEP(5) to wait 5 seconds before doing the drop user.

share|improve this answer
    
Thanks for your advice, but I'm still getting the error. Please see updated answer. –  Jake Feasel Feb 8 '12 at 18:03
add comment

I encountered the same problem and as a novice PL/SQL programmer, ended up with the following. Please correct if I'm wrong, but killing the sessions and waiting in a loop for them to clear out might not always work - what if someone connects while we're waiting for Oracle to clear the sessions?

DECLARE
  v_user_exists NUMBER;
  user_name CONSTANT varchar2(20) := 'USERNAME_GOES_HERE';
BEGIN
  LOOP

    -- Kill any currently active sessions
    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;

    -- Try to drop the user.
    BEGIN
      EXECUTE IMMEDIATE 'drop user ' || user_name || ' cascade';
      EXCEPTION WHEN OTHERS THEN
      IF (SQLCODE = -1940) THEN
        -- Ignore ORA-01940 'Cannot drop user that is currently connected' exception
        NULL;
      ELSE
        RAISE;
      END IF;
    END;

    -- Continue looping, killing sessions and retrying to drop until successful
    BEGIN
      SELECT COUNT(*) INTO v_user_exists FROM dba_users WHERE username = user_name;
      EXIT WHEN v_user_exists = 0;
    END;
  END LOOP;
END;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.