5 Replies Latest reply: Jun 17, 2008 11:38 AM by sape007 RSS

    Deadlock by MMON on Oracle table WRH$_SQLTEXT

    sape007
      Using: RedHat 4, Oracle 10.2.0.1.0 (we have plans to apply latest patches, it’s still on dev)

      On my live system I have what looks like a Deadlock (lock has been there for last 40min). see blow both processes are Oracle MMON's

      I am not able to kill the sessions, how else gracefully can I get rid of Oracle processes/lock, without db restart.
      SQL> select * from v$lock where type in ('TM', 'TX', 'UL')

      ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
      ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
      000000013D3A2AF8 000000013D3A2B18        702 TX     393238     157194          0          6      49340          0
      000000013D3A2B90 000000013D3A2BB0        835 TX     327682     167109          0          6      49340          0
      000000013B977B68 000000013B977B90        835 TM       8933          0          3          0      49341          0
      000000013B977C68 000000013B977C90        702 TM       8933          0          3          0      49340          0
      000000013BB4BA18 000000013BB4BBA0        835 TX     393238     157194          6          0      49341          1
      000000013BCF9578 000000013BCF9700        702 TX     327682     167109          6          0      49340          1

      6 rows selected.


      SQL> select sid, serial#, username, program from v$session where sid in(835,702);

             SID    SERIAL# USERNAME                       PROGRAM
      ---------- ---------- ------------------------------ ------------------------------------------------
             702         51                                oracle@xxprodxx.cust (m000)
             835         49                                oracle@xxprodxx.cust (m001)


      SQL> show user;
      USER is "SYS"

      SQL> alter system kill session '835,49';
      alter system kill session '835,49'
      *
      ERROR at line 1:
      ORA-00029: session is not a user session
      Thanks in advance for any suggestions / fixes.