Shulou(Shulou.com)06/01 Report--

How to kill all processes under a user and drop that 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/change_on_install@orcl 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 is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you 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/change_on_install@orcl 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 10 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/test@orcl

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL >

Transferred from MOS

How to kill all processes under a user and drop that 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/change_on_install@orcl 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 is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you 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/change_on_install@orcl 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 10 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/test@orcl

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL >

Transferred 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/change_on_install@orcl 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 is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you 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/change_on_install@orcl 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 10 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/test@orcl

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL >

Transferred from MOS

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.