Today encountered a problem, there is a table can not operate, it is likely to be locked, the first thought is kill session, so executed the following script to find out which session has a problem:
To see if a table is locked
SELECT/*+ rule*/
a.sid, B.owner, object_name, object_type from
v$lock A, all_objects b
WHERE type = ' TM '
and a.id1 = b.object_id;
According to the SID queried above, find the corresponding serial#:
SELECT sid,serial# from v$session WHERE sid = &sid;
A session was found to have a lock SID 197,serial# 17, and then execute ALTER system kill session ' 197,17 '; in about 30s, Pl/sql developer reported an error: ora-00031: Mark the sessions to terminate.
WORKAROUND: Detect the SPID of the session
Select spid, Osuser, s.program from V$session S, v$process p where s.paddr = p.addr and s.sid = 197;
1. On Linux, Kill-9 12345
2. On Windows, C:\Documents and Settings\gg>orakill ORCL 12345
ORCL: Represents the instance name of the process to kill
12345: is to kill the thread number
Ora-00031:session marked for Kill
Cause:the session specified in a ALTER SYSTEM KILL session command cannot bekilled immediately (because it is rolling BA CK or blocked on a networkoperation) and but it has been for kill. This means it'll be the killed as Soonas possible after its current uninterruptible operation.
Action:no action is required for the session to be killed, but further executionsof the ALTER SYSTEM KILL session command On this session could cause the Sessionto be killed sooner.
Here are the additions:
Oracle compiled a package found that as long as a compilation of the card is dead, and later use the Oracle query, kill lock Resource SQL statement method to check the lock when found that some of the resources used in the package is always in a deadlock state. It was later unlocked by alter system kill session, but found the problem shown in Figure 1 below:
ORA-00031: Mark the session to terminate
ORA-00031: Mark the session to terminate
Later, by connecting to the system on which the Oracle database resides, and then killing the process, the following methods are resolved:
(1) Query the SID and serial# of the locked resource:
SELECT S.sid,
s.serial#,
v.*,
ao.* from
V$locked_object V,
all_objects ao,
v$session s
WHERE v.object_id = ao.object_id and
s.sid = v.session_id;
(2) After using the above SQL query SID and serial#, use the following SQL to kill session:
-If a record represents lock, the SID and Serial# are recorded, and the SID and serial# under the record are replaced with the following 738,1429 to contact lock.
ALTER SYSTEM KILL session ' 738,1429 ';
(3) If the kill session above is listed as the error in Figure 1 above, then use the following SQL to investigate the corresponding SPID:
SELECT P.spid,
s.osuser,
S.program from
v$session s,
v$process p
WHERE s.paddr = p.addr
and s.sid = 37;--Replace with the corresponding session_id
(4) Connect to the system where the Oracle database is located (I am a Linux system), record the SPID found above (suppose it is 1133), and use the following statement to kill the process:
Kill-9 1133
Wait for a while, after the process kill successfully, then check the lock to find that the resources have been released. If Oracle is installed on a Windows system, use the Windows system's kill process to kill the process.