Oracle ORA-00031: session marked for kill (marking the session to be terminated) solution, ora-00031marked

Oracle ORA-00031: session marked for kill (marking the session to be terminated) solution, ora-00031marked

Today, I encountered a problem where a table cannot be operated and may be locked. The first thing I thought was to kill the session. So I executed the following script to find out which session has a problem:

Check whether the table is locked

SELECT /*+ rule*/a.sid, b.owner, object_name, object_typeFROM v$lock a, all_objects bWHERE TYPE = 'TM'and a.id1 = b.object_id;

Find the corresponding serial according to the sid found above #:
SELECT sid, serial # FROM v $ session WHERE sid = & sid;

We found that there was a session with the lock sid 197, serial #17, so execute alter system kill session '2017, 17'; probably waited for 30 s, pl/SQL developer reported an error: ora-00031: mark the session to terminate.

Solution: Check 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. in linux, kill-9 12345
2. On windows, C: \ Documents ents and Settings \ gg> orakill orcl 12345

Orcl: Instance name of the process to be killed
12345: indicates the thread number to be killed.

ORA-00031: session marked for kill
Cause: The session specified in an alter system kill session command cannot bekilled immediately (because it is rolling back or blocked on a networkoperation), but it has been marked for kill. this means it will be killed as soonas possible after its current uninterruptible operation is done.
Action: No action is required for the session to be killed, but further executionsof the alter system kill session command on this session may cause the sessionto be killed sooner.

Below is a supplement:

When a package is compiled in oracle, it is found that as long as a compilation is complete, later, when I used the SQL statement for oracle query and Kill resource lock, I found that some resources in the package were in a deadlock state. Later, the alter system kill session method was used to unlock the session, but the problem shown in 1 was discovered:
ORA-00031: marking the session to terminate

ORA-00031: marking the session to terminate

Then, you can connect to the system where the oracle database is located and kill the process to solve the problem as follows:

(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) Use the preceding SQL statement to query sid and serial #, and then use the following SQL statement to kill the session:

-- If there is a record, it indicates lock. Record SID and serial #. Replace the SID and serial # in the record with the 738,1429 below to access lock.
Alter system kill session '2017 9 ';

(3) If the preceding kill session reports an error such as 1, use the following SQL statement to investigate the spid corresponding to the session:

SELECT p. spid, s. osuser, s. program FROM v $ session s, v $ process p WHERE s. paddr = p. addr AND s. sid = 37; -- replace it with the corresponding session_id

(4) connect to the system where the oracle database is located (I am using a Linux system) and record the spid found above (assuming 1133 is found here ), run the following statement to kill the process:

Kill-9 1133

Wait for a while. After the process is killed, check the lock to find that the resource has been released. If oracle is installed on Windows, kill the process in Windows.