Oracle Database case study-Oracle system running failure-unable to release ended process resources leading to process deadlock

1.1 symptom description

Query the Process status of the SQL statement executed by the database and find that the SQL statement execution process is deadlocked. The system displays the following information:

SQL> select address, SQL _text, piece, SECONDS_IN_WAIT, SERVICE_NAME, MACHINE, PROCESS, LOCKWAIT from v $ session, v $ sqltext where address = SQL _address and SQL _text like '% FM _ %' order by address, piece;

ADDRESS  SQL_TEXT                                                         SECONDS_IN_WAIT SERVICE_NAME              PROCESS                  LOCKWAIT

-------- ---------------------------------------------------------------- --------------- ----------------------------------------------------------- 

761BA4D0 DELETE FROM NE5_1.TBL_FM_ALARM_LOG WHERE ROWID IN (SELECT ROWID             7807 omu                       22927                    AEC028A8

761BA4D0 FROM (SELECT ROWID FROM NE5_1.TBL_FM_ALARM_LOG             WHERE            7807 omu                       22927                    AEC028A8

9AB0B8F8 ress=sql_address and sql_text like '%FM_%' order   by   address,               0 SYS$USERS                 27667

A311D7E4 UPDATE NE5_1.TBL_FM_ALARM_LOG                                 SE            7658 omu                       29835                    AEC027C0

From the above information, we can see that the 22927 process first enters the write lock status, and the 29835 process attempts to write the lock again in an infinite wait state.

1.2 possible causes

Too many resources have not been submitted for closed applications, and PMON takes a lot of time to process rollback. As a result, the lock resources cannot be processed and released.

1.3 Procedure

1. ToOracleThe machine on which the user logs on to the database.
2. ToSysdbaThe user connects to the database.
3. Query the session IDs and SERIAL of a process ".

SQL> select sess. sid, sess. serial # from v $ session sess, v $ process proc where sess. paddr = proc. addr and proc. spid = '2016 ';

The system prompts the following information:

       SID    SERIAL#

---------- ----------

       137          5

The preceding information shows that the session ID of process 22927 is 137 and the SERIAL is 5.

4. Disable this session.

SQL> alter system kill session '2017, 5 ';

1.4 references

PMON is short for Process Monitor. PMON first rolls back uncommitted resources, and then releases the locks and other resources held by the interrupted connection.