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!
Best Answer
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.
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.