0 Replies Latest reply on Sep 16, 2015 9:13 AM by 2778269

    GV$locked_object showing object locked but kill session is giving ORA-00030: User session ID does not exist

    2778269

      16         gv$locked_object b

      17  WHERE  a.object_id = b.object_id

      18  ORDER BY 1, 2, 3, 4;

       

       

         INST_ID        SID USERNAME             OBJECT_OWNER         OBJECT_NAME                    LOCKED_MODE     OS_USER_NAME

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

               2         17 CCMETL               CCMETL               TRA_CSG_HSE_BASE               Row-X (SX)      oracle

       

       

      SQL>  select * from gv$locked_object;

       

       

         INST_ID     XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME                OS_USER_NAME                   PROCESS                  LOCKED_MODE

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

               2         86          5         70     134281         17 CCMETL                         oracle                         9575                      ##########

      SQL> select sid,serial#,inst_id ,username,sql_id from gv$session where sid=17 and USERNAME='CCMETL';

       

       

             SID    SERIAL#    INST_ID USERNAME                       SQL_ID

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

              17      26854          2 CCMETL

      SQL> alter system kill session '17,26854';

      alter system kill session '17,26854'

      *

      ERROR at line 1:

      ORA-00030: User session ID does not exist.

       

      sh-4.1$ ps -ef |grep 9575

      oracle   26587  7285  0 09:00 pts/4    00:00:00 grep 9575