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).

Why a tech blog?

And why a tech blog at that and not just any normal blog?

Well lets just say that 3 years worth of work related archive just got lost due to some stupid computer crash and it isn’t helping that my DVD backup became corrupted.

My work archive contained all the technical related documentation from Cobol to Oracle, DB2, SQL Server databases that I’ve encountered since i started working. Which contained common and rare database bugs(and how to solve them), database pl/sql scripts, Unix shell scripts, dos scripts, and helluva tons of soft copy documentation to make my work a million times easier. But all of that went *poof* the moment my computer crashed and my DVD disc started to decompose (yeah they don’t last forever http://www.gcn.com/print/23_5/25166-1.html?topic=news). Seems like i need to handle my DVD backups with more care than usual. LOL!

So i decided to make a tech blog. This should be easier to backup and maintain i hope. 😀

Welcome to my tech blog. Feel free to add your comments on all my posts.