One of the users in the PROD database, Adams, complains that his update on the table, TRANS, is
taking an unusually long time to complete. You find that the table gets locked by another database
user before Adams starts his transactions, and you are unable to contact the user holding the table
lock. As Adams is updating some crucial rows in the table, he should get the highest priority.
Which method would you use to overcome this problem?
A.
execute the command, ALTER SESSION KILL .., to kill the blocking session
B.
execute the DBMS_SESSION.KILL_SESSION procedure to kill the blocking session
C.
execute the command, ALTER SYSTEM KILL SESSION .., to kill the blocking session
D.
execute the command, ALTER SESSION UNLOCK .., to release the lock for the blocking session
E.
execute the command, ALTER SYSTEM UNLOCK SESSION .., to release the lock for the blocking
session
In Oracle the alter system kill session command allows you to kill an Oracle session.
The alter system kill session command requires two unique arguments that uniquely identify the Oracle session, the session identifier and serial number.
First you have to identify the session to be killed with alter system kill session.
The SID and SERIAL# values of the Oracle session to be killed can then be substituted and the alter system kill session command issued.
SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’;
Sometimes Oracle.exe is not able to kill the session immediately with the alter system kill session command alone. Upon issuing the alter system kill session command, the session will be ‘marked for kill’. It will then be killed as soon as possible.
DE are wrong, the only way to unlock the unknown session is to use ALTER SYSTEM KILL SESSION.
There are a number of ways to kill rogue sessions both within Oracle and externally.
Identify the Session to be Killed
ALTER SYSTEM KILL SESSION
ALTER SYSTEM DISCONNECT SESSION
The Windows Approach
The UNIX Approach