Dropping user in oralce by killing active sessions
Today when i was looking through the metalink , i found this script, which seems to be a useful script for DBA’s.
The following PL/SQL script is intended to be used for immediate removal of a user from the database. It kills all active sessions and then drops the user from the database. I have tested in 10.2, it works fine
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;
/
Configuring the Sample Code
This script is intended to be run by the SYS user of the database.
Running the Sample Code
1. Copy the sample code below into a file named kill_drop_user.sql.
2. Open SQL*Plus and connect as user SYS to your database
SQL> CONNECT sys/change_on_install@orcl AS SYSDBA
3. Create a user called TEST with password TEST
SQL> GRANT connect, resource TO test IDENTIFIED BY test;
4. Create the procedure kill_drop_user
SQL> @”C:\scripts\kill_drop_user.sql”
5. Open three (3) SQL*Plus sessions and connect as user TEST.
6. Execute the PL/SQL script
SQL> SET serveroutput ON size 1000000
SQL> SET timing ON
SQL> EXEC kill_drop_user(‘TEST’);
The above script may be a good help for you.
Partho Konar
