How to kill a database user.

By “killing” i meant disconnecting the user from the database forcefully.

For Oracle databases

You can either use the Oracle Enterprise Manager console to disconnect the user OR If you don’t have an Oracle Enterprise Manager setup for that database you can just login to the database as sys or system. You can use Sqlplus, Sqldeveloper, Toad or any one of those database clients to connect to the database.

Then issue a select command to get the information on the user you want to kill/disconnect from the database:

select SID, SERIAL#, username, osuser, machine from v$session;

username = database username
osuser = client operating system user that is connecting to the database
machine = client machine IP.

Remember the SID and SERIAL# of the user(s) that you with to kill/disconnect. Then issue a kill command.

Alter system kill session ‘SID,SERIAL#’;

For DB2 UDB databases.

Login normally to the DB2 UDB database as dbadm or as instance owner or database owner then issue a list command to get the information on the user you want to kill/disconnect from the database:

list applications for database database_name

For a more detailed output (if you want to check for a more detailed output use the “get snapshot for all databases” command instead of list applications)

Take note of the application handle number and use it to issue a kill/disconnect command.

force application (application_handle_number)

Other notes when disconnecting/killing database users:
– IF all else fails, you can always restart the database to kill off those pesky users.
– IF you do happen to have a user that has auto connect script/or maybe and application that automatically reconnects to the database. It is advisable to revoke the connect privilege of that user to disable it from reconnecting again.
– OR you use an ORACLE database you can start the database in restricted mode.
– OR you use a DB2 UDB database you can use the quiesce command.
– Take note that the user might not be disconnected right away since the database has to rollback all the uncommitted data of the user you disconnected(it might take a while if the user has tons of uncommitted data).