How to kill all the processes under a user and drop the user

How to kill all the processes under a user and drop the user


Copy the sample code below into a file named Kill_drop_user.sql.
Open Sql*plus and connect as user SYS to your database
Sql> CONNECT Sys/[email protected] as SYSDBA
Create a user called Test with password test
sql> GRANT Connect, resource to test identified by test;
Create the procedure Kill_drop_user
sql> @ "C:\scripts\kill_drop_user.sql"
Open three (3) Sql*plus sessions and connect as user TEST.
Execute the PL/SQL script
Sql> set serveroutput on size 1000000 sql> SET timing on sql> EXEC kill_drop_user (' TEST ');
CAUTION


This sample code was provided for educational purposes only, and was not supported by Oracle support. It has been tested internally, however, we don't guarantee that it'll work for you. Ensure that the run it in your test environment before using.
SAMPLE CODE


CREATE OR REPLACE PROCEDURE kill_drop_user (in_username in VARCHAR2,
Sleep_interval in number DEFAULT 10)
As
PRAGMA autonomous_transaction;


Cannot_drop_user EXCEPTION;
PRAGMA Exception_init (Cannot_drop_user,-1940);


User_count Number: =-1;


BEGIN


SELECT COUNT (*) into User_count from dba_users WHERE username = in_username;
IF user_count = 0 Then
Dbms_output. Put_Line (' User ' | | in_username | | ' does not exist. ');
RETURN;
END IF;


For I in (SELECT Sid, serial# from v$session WHERE username = in_username) LOOP
EXECUTE IMMEDIATE ' alter system kill session ' | | "' | | I.sid | | ', ' | | i.serial# | | "' Immediate ';
Dbms_output. Put_Line (' Killing user ' | | i.sid | | ', ' | | i.serial#);
END LOOP;


LOOP
BEGIN
Dbms_output. Put_Line (' Attempting to drop user ' | | in_username | | ‘...‘);
EXECUTE IMMEDIATE ' DROP USER ' | | In_username | | ' CASCADE ';


EXIT when SQLCODE <>-1940;
EXCEPTION
When Cannot_drop_user Then
--dbms_output. Put_Line (SQLERRM);
Dbms_output. Put_Line (' Waiting ' | | sleep_interval | | ' seconds for resource clean-up ... ');
Dbms_lock. SLEEP (Sleep_interval);
When OTHERS Then
Dbms_output. Put_Line (' Inner: ' | | SQLERRM);
END;
END LOOP;
Dbms_output. Put_Line (' Exiting loop with SQLCODE: ' | | SQLCODE);
Dbms_output. Put_Line (' User ' | | in_username | | ' has been dropped. ');


EXCEPTION
When OTHERS Then
Dbms_output. Put_Line (' Outer: ' | | SQLERRM);
END;
/
SAMPLE OUTPUT


Sql*plus:release 10.1.0.4.0-production on Tue Sep 6 15:34:47 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0-production
With the partitioning, OLAP and Data Mining options


Sql> CONNECT Sys/[email protected] as SYSDBA
Connected.
sql> GRANT Connect, resource to test identified by test;


Grant succeeded.


sql> @ "C:\scripts\kill_drop_user.sql"
Procedure created.


Sql> SET serveroutput on size 1000000


Sql> SET Timing on


sql> EXEC kill_drop_user (' TEST ');
Killing user 152, 6915
Killing user 153, 326
Killing user 154, 156
Attempting to drop user TEST ...
Waiting seconds for resource clean-up ...
Attempting to drop user TEST ...
Exiting Loop with sqlcode:0
User TEST has been dropped.


PL/SQL procedure successfully completed.


elapsed:00:00:10.71


Sql>--Verify user has been dropped
Sql> CONNECT Test/[email protected]
ERROR:
Ora-01017:invalid Username/password; Logon denied




Warning:you is no longer connected to ORACLE.
Sql>






Turn from MoS
How to kill all the processes under a user and drop the user


Copy the sample code below into a file named Kill_drop_user.sql.
Open Sql*plus and connect as user SYS to your database
Sql> CONNECT Sys/[email protected] as SYSDBA
Create a user called Test with password test
sql> GRANT Connect, resource to test identified by test;
Create the procedure Kill_drop_user
sql> @ "C:\scripts\kill_drop_user.sql"
Open three (3) Sql*plus sessions and connect as user TEST.
Execute the PL/SQL script
Sql> set serveroutput on size 1000000 sql> SET timing on sql> EXEC kill_drop_user (' TEST ');
CAUTION


This sample code was provided for educational purposes only, and was not supported by Oracle support. It has been tested internally, however, we don't guarantee that it'll work for you. Ensure that the run it in your test environment before using.
SAMPLE CODE


CREATE OR REPLACE PROCEDURE kill_drop_user (in_username in VARCHAR2,
Sleep_interval in number DEFAULT 10)
As
PRAGMA autonomous_transaction;


Cannot_drop_user EXCEPTION;
PRAGMA Exception_init (Cannot_drop_user,-1940);


User_count Number: =-1;


BEGIN


SELECT COUNT (*) into User_count from dba_users WHERE username = in_username;
IF user_count = 0 Then
Dbms_output. Put_Line (' User ' | | in_username | | ' does not exist. ');
RETURN;
END IF;


For I in (SELECT Sid, serial# from v$session WHERE username = in_username) LOOP
EXECUTE IMMEDIATE ' alter system kill session ' | | "' | | I.sid | | ', ' | | i.serial# | | "' Immediate ';
Dbms_output. Put_Line (' Killing user ' | | i.sid | | ', ' | | i.serial#);
END LOOP;


LOOP
BEGIN
Dbms_output. Put_Line (' Attempting to drop user ' | | in_username | | ‘...‘);
EXECUTE IMMEDIATE ' DROP USER ' | | In_username | | ' CASCADE ';


EXIT when SQLCODE <>-1940;
EXCEPTION
When Cannot_drop_user Then
--dbms_output. Put_Line (SQLERRM);
Dbms_output. Put_Line (' Waiting ' | | sleep_interval | | ' seconds for resource clean-up ... ');
Dbms_lock. SLEEP (Sleep_interval);
When OTHERS Then
Dbms_output. Put_Line (' Inner: ' | | SQLERRM);
END;
END LOOP;
Dbms_output. Put_Line (' Exiting loop with SQLCODE: ' | | SQLCODE);
Dbms_output. Put_Line (' User ' | | in_username | | ' has been dropped. ');


EXCEPTION
When OTHERS Then
Dbms_output. Put_Line (' Outer: ' | | SQLERRM);
END;
/
SAMPLE OUTPUT


Sql*plus:release 10.1.0.4.0-production on Tue Sep 6 15:34:47 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0-production
With the partitioning, OLAP and Data Mining options


Sql> CONNECT Sys/[email protected] as SYSDBA
Connected.
sql> GRANT Connect, resource to test identified by test;


Grant succeeded.


sql> @ "C:\scripts\kill_drop_user.sql"
Procedure created.


Sql> SET serveroutput on size 1000000


Sql> SET Timing on


sql> EXEC kill_drop_user (' TEST ');
Killing user 152, 6915
Killing user 153, 326
Killing user 154, 156
Attempting to drop user TEST ...
Waiting seconds for resource clean-up ...
Attempting to drop user TEST ...
Exiting Loop with sqlcode:0
User TEST has been dropped.


PL/SQL procedure successfully completed.


elapsed:00:00:10.71


Sql>--Verify user has been dropped
Sql> CONNECT Test/[email protected]
ERROR:
Ora-01017:invalid Username/password; Logon denied




Warning:you is no longer connected to ORACLE.
Sql>






Turn from MoS


Copy the sample code below into a file named Kill_drop_user.sql.
Open Sql*plus and connect as user SYS to your database
Sql> CONNECT Sys/[email protected] as SYSDBA
Create a user called Test with password test
sql> GRANT Connect, resource to test identified by test;
Create the procedure Kill_drop_user
sql> @ "C:\scripts\kill_drop_user.sql"
Open three (3) Sql*plus sessions and connect as user TEST.
Execute the PL/SQL script
Sql> set serveroutput on size 1000000 sql> SET timing on sql> EXEC kill_drop_user (' TEST ');
CAUTION


This sample code was provided for educational purposes only, and was not supported by Oracle support. It has been tested internally, however, we don't guarantee that it'll work for you. Ensure that the run it in your test environment before using.
SAMPLE CODE


CREATE OR REPLACE PROCEDURE kill_drop_user (in_username in VARCHAR2,
Sleep_interval in number DEFAULT 10)
As
PRAGMA autonomous_transaction;


Cannot_drop_user EXCEPTION;
PRAGMA Exception_init (Cannot_drop_user,-1940);


User_count Number: =-1;


BEGIN


SELECT COUNT (*) into User_count from dba_users WHERE username = in_username;
IF user_count = 0 Then
Dbms_output. Put_Line (' User ' | | in_username | | ' does not exist. ');
RETURN;
END IF;


For I in (SELECT Sid, serial# from v$session WHERE username = in_username) LOOP
EXECUTE IMMEDIATE ' alter system kill session ' | | "' | | I.sid | | ', ' | | i.serial# | | "' Immediate ';
Dbms_output. Put_Line (' Killing user ' | | i.sid | | ', ' | | i.serial#);
END LOOP;


LOOP
BEGIN
Dbms_output. Put_Line (' Attempting to drop user ' | | in_username | | ‘...‘);
EXECUTE IMMEDIATE ' DROP USER ' | | In_username | | ' CASCADE ';


EXIT when SQLCODE <>-1940;
EXCEPTION
When Cannot_drop_user Then
--dbms_output. Put_Line (SQLERRM);
Dbms_output. Put_Line (' Waiting ' | | sleep_interval | | ' seconds for resource clean-up ... ');
Dbms_lock. SLEEP (Sleep_interval);
When OTHERS Then
Dbms_output. Put_Line (' Inner: ' | | SQLERRM);
END;
END LOOP;
Dbms_output. Put_Line (' Exiting loop with SQLCODE: ' | | SQLCODE);
Dbms_output. Put_Line (' User ' | | in_username | | ' has been dropped. ');


EXCEPTION
When OTHERS Then
Dbms_output. Put_Line (' Outer: ' | | SQLERRM);
END;
/
SAMPLE OUTPUT


Sql*plus:release 10.1.0.4.0-production on Tue Sep 6 15:34:47 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0-production
With the partitioning, OLAP and Data Mining options


Sql> CONNECT Sys/[email protected] as SYSDBA
Connected.
sql> GRANT Connect, resource to test identified by test;


Grant succeeded.


sql> @ "C:\scripts\kill_drop_user.sql"
Procedure created.


Sql> SET serveroutput on size 1000000


Sql> SET Timing on


sql> EXEC kill_drop_user (' TEST ');
Killing user 152, 6915
Killing user 153, 326
Killing user 154, 156
Attempting to drop user TEST ...
Waiting seconds for resource clean-up ...
Attempting to drop user TEST ...
Exiting Loop with sqlcode:0
User TEST has been dropped.


PL/SQL procedure successfully completed.


elapsed:00:00:10.71


Sql>--Verify user has been dropped
Sql> CONNECT Test/[email protected]
ERROR:
Ora-01017:invalid Username/password; Logon denied




Warning:you is no longer connected to ORACLE.
Sql>






Turn from MoS

How to kill all the processes under a user and drop the user