1 2 Previous Next 18 Replies Latest reply on Sep 10, 2012 6:29 PM by jgarry

    Object locked in v$locked_object but can't find session/process blocking

    DroidDr
      Hi,

      I am on database 10.2.0.5, windows x64.

      A developer calls me and tells me there are locks on 3 tables, and the locks are not released.
      So I run this to see which objects are locked by which session
      select vlo.object_id, vlo.session_id, vlo.oracle_username, vlo.process
        , DECODE(vlo.LOCKED_MODE, 0,'NONE', 1,'NULL', 2,'ROW SHARE', 3,'ROW EXCLUSIVE', 4,'SHARE', 5,'SHARE ROW EXCLUSIVE', 6,'EXCLUSIVE', NULL) LOCK_MODE 
        , do.owner, do.object_name, do.object_type
        , vs.saddr, vs.serial#, vs.paddr, vs.username, vs.ownerid, vs.status, vs.server, vs.schemaname, vs.osuser, vs.machine, vs.program, vs.type, vs.logon_time, vs.last_call_et
        , vs.blocking_session_status, vs.event#, vs.event, vs.wait_class#, vs.wait_class, vs.wait_time, vs.seconds_in_wait, vs.state
        from v$locked_object vlo
          inner join dba_objects do on (vlo.object_id = do.object_id)
          left outer join v$session vs on (vlo.session_id = vs.sid)
      -----------
      I get (after finding out my table locks come from SID 514)
      OBJECT_ID              SESSION_ID             ORACLE_USERNAME                PROCESS      LOCK_MODE           OWNER                          OBJECT_NAME                                                                                                                      OBJECT_TYPE         SADDR            SERIAL#                PADDR            USERNAME                       OWNERID                STATUS   SERVER    SCHEMANAME                     OSUSER                         MACHINE                                                          PROGRAM                                                          TYPE       LOGON_TIME                LAST_CALL_ET           BLOCKING_SESSION_STATUS EVENT#                 EVENT                                                            WAIT_CLASS#            WAIT_CLASS                                                       WAIT_TIME              SECONDS_IN_WAIT        STATE               
      ---------------------- ---------------------- ------------------------------ ------------ ------------------- ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- ---------------- ---------------------- ---------------- ------------------------------ ---------------------- -------- --------- ------------------------------ ------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ------------------------- ---------------------- ----------------------- ---------------------- ---------------------------------------------------------------- ---------------------- ---------------------------------------------------------------- ---------------------- ---------------------- ------------------- 
      373122                 514                    IRMFIN                         22AB7298     ROW EXCLUSIVE       IRMFIN                         IC_PRODUCT_STATUS                                                                                                                TABLE               000007FF3E1A9070 1301                   000007FF3E639120 IRMFIN                         2147483644             INACTIVE DEDICATED IRMFIN                         IRMTEST                        IRM                                                              DSA0:[GEMBASE730.][RUN]GEM.EXE                                   USER       2012/09/05 13:55:51       702                    NO HOLDER               263                    SQL*Net message from client                                      6                      Idle                                                             0                      702                    WAITING             
      373025                 514                    IRMFIN                         22AB7298     ROW EXCLUSIVE       IRMFIN                         IC_BIN_DETAIL_STATUS                                                                                                             TABLE               000007FF3E1A9070 1301                   000007FF3E639120 IRMFIN                         2147483644             INACTIVE DEDICATED IRMFIN                         IRMTEST                        IRM                                                              DSA0:[GEMBASE730.][RUN]GEM.EXE                                   USER       2012/09/05 13:55:51       702                    NO HOLDER               263                    SQL*Net message from client                                      6                      Idle                                                             0                      702                    WAITING             
      373055                 514                    IRMFIN                         22AB7298     ROW EXCLUSIVE       IRMFIN                         IC_LOT_STATUS                                                                                                                    TABLE               000007FF3E1A9070 1301                   000007FF3E639120 IRMFIN                         2147483644             INACTIVE DEDICATED IRMFIN                         IRMTEST                        IRM                                                              DSA0:[GEMBASE730.][RUN]GEM.EXE                                   USER       2012/09/05 13:55:51       702                    NO HOLDER               263                    SQL*Net message from client                                      6                      Idle                                                             0                      702                    WAITING             
      -----------
      I run it again and I get something different, this time the lock is still there, but no session. select * from v$session where sid= 514 returns nothing.
      I get
      OBJECT_ID              SESSION_ID             ORACLE_USERNAME                PROCESS      LOCK_MODE           OWNER                          OBJECT_NAME                                                                                                                      OBJECT_TYPE         SADDR            SERIAL#                PADDR            USERNAME                       OWNERID                STATUS   SERVER    SCHEMANAME                     OSUSER                         MACHINE                                                          PROGRAM                                                          TYPE       LOGON_TIME                LAST_CALL_ET           BLOCKING_SESSION_STATUS EVENT#                 EVENT                                                            WAIT_CLASS#            WAIT_CLASS                                                       WAIT_TIME              SECONDS_IN_WAIT        STATE               
      ---------------------- ---------------------- ------------------------------ ------------ ------------------- ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- ---------------- ---------------------- ---------------- ------------------------------ ---------------------- -------- --------- ------------------------------ ------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ------------------------- ---------------------- ----------------------- ---------------------- ---------------------------------------------------------------- ---------------------- ---------------------------------------------------------------- ---------------------- ---------------------- ------------------- 
      373122                 514                                                                ROW EXCLUSIVE       IRMFIN                         IC_PRODUCT_STATUS                                                                                                                TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
      373025                 514                                                                ROW EXCLUSIVE       IRMFIN                         IC_BIN_DETAIL_STATUS                                                                                                             TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
      373055                 514                                                                ROW EXCLUSIVE       IRMFIN                         IC_LOT_STATUS                                                                                                                    TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
      When I run the query later, I sometimes get another session, but the lock stays. It seems here, that I am catching the same SID on new sessions, but it is probably a different serial # than the original session locking the table rows.

      I get nothing from
      select * from dba_waiters;
      select * from dba_blockers;

      Now why are those 3 tables row locked on some rows and I can't find the session responsible? The developer tells me his application crashed and the rows are locked since.

      So far ( keep in mind I am not a locking expert) the only way I found to release the locks is a DB bounce, its a test DB so no biggy.

      Is this a bug? Am I missing something?
      This is the second time this has happened. How can I solve this issue?


      Best wishes to all.
        • 1. Re: Object locked in v$locked_object but can't find session/process blocking
          sb92075
          SELECT Decode(request, 0, 'Holder: ', 
                                 'Waiter: ') 
                 ||vl.sid sess, 
                 status, 
                 id1, 
                 id2, 
                 lmode, 
                 request, 
                 vl.TYPE 
          FROM   v$lock vl, 
                 v$session vs 
          WHERE  ( id1, id2, vl.TYPE ) IN (SELECT id1, 
                                                  id2, 
                                                  TYPE 
                                           FROM   v$lock 
                                           WHERE  request > 0) 
                 AND vl.sid = vs.sid 
          ORDER  BY id1, 
                    request 
          
          / 
          any rows returned indicate a problem
          • 2. Re: Object locked in v$locked_object but can't find session/process blocking
            DroidDr
            No rows were return by your query sb92075

            Thanks.
            • 3. Re: Object locked in v$locked_object but can't find session/process blocking
              DroidDr
              Any other ideas why I can't identify the source of the locks?
              • 4. Re: Object locked in v$locked_object but can't find session/process blocking
                Fran
                Try with this script:
                select s.username, s.sid, s.serial#,
                       s.osuser, k.ctime, o.object_name object, k.kaddr,
                case l.locked_mode when 1 then 'No Lock'
                                   when 2 then 'Row Share'
                                   when 3 then 'Row Exclusive'
                                   when 4 then 'Shared Table'
                                   when 5 then 'Shared Row Exclusive'
                                   when 6 then 'Exclusive'
                end locked_mode,
                case
                when k.type = 'AE' then 'Application Edition'
                when k.type = 'BL' then 'Buffer Cache Management (PCM lock)'
                when k.type = 'CF' then 'Controlfile Transaction'
                when k.type = 'CI' then 'Cross Instance Call'
                when k.type = 'CU' then 'Bind Enqueue'
                when k.type = 'DF' then 'Data File'
                when k.type = 'DL' then 'Direct Loader'
                when k.type = 'DM' then 'Database Mount'
                when k.type = 'DR' then 'Distributed Recovery'
                when k.type = 'DX' then 'Distributed Transaction'
                when k.type = 'FS' then 'File Set'
                when k.type = 'IN' then 'Instance Number'
                when k.type = 'IR' then 'Instance Recovery'
                when k.type = 'IS' then 'Instance State'
                when k.type = 'IV' then 'Library Cache Invalidation'
                when k.type = 'JQ' then 'Job Queue'
                when k.type = 'KK' then 'Redo Log Kick'
                when k.type like 'L%' then 'Library Cache Lock'
                when k.type = 'MM' then 'Mount Definition'
                when k.type = 'MR' then 'Media Recovery'
                when k.type like 'N%' then 'Library Cache Pin'
                when k.type = 'PF' then 'Password File'
                when k.type = 'PI' then 'Parallel Slaves'
                when k.type = 'PR' then 'Process Startup'
                when k.type = 'PS' then 'Parallel slave Synchronization'
                when k.type like 'Q%' then 'Row Cache Lock'
                when k.type = 'RT' then 'Redo Thread'
                when k.type = 'SC' then 'System Commit number'
                when k.type = 'SM' then 'SMON synchronization'
                when k.type = 'SN' then 'Sequence Number'
                when k.type = 'SQ' then 'Sequence Enqueue'
                when k.type = 'SR' then 'Synchronous Replication'
                when k.type = 'SS' then 'Sort Segment'
                when k.type = 'ST' then 'Space Management Transaction'
                when k.type = 'SV' then 'Sequence Number Value'
                when k.type = 'TA' then 'Transaction Recovery'
                when k.type = 'TM' then 'DML Enqueue'
                when k.type = 'TS' then 'Table Space (or Temporary Segment)'
                when k.type = 'TT' then 'Temporary Table'
                when k.type = 'TX' then 'Transaction'
                when k.type = 'UL' then 'User-defined Locks'
                when k.type = 'UN' then 'User Name'
                when k.type = 'US' then 'Undo segment Serialization'
                when k.type = 'WL' then 'Writing redo Log'
                when k.type = 'XA' then 'Instance Attribute Lock'
                when k.type = 'XI' then 'Instance Registration Lock'
                 end type
                from v$session s, sys.v_$_lock c, sys.v_$locked_object l, dba_objects o, sys.v_$lock k
                where o.object_id = l.object_id
                and l.session_id = s.sid
                and k.sid = s.sid
                and s.saddr = c.saddr
                and k.kaddr = c.kaddr
                and k.lmode = l.locked_mode
                and k.lmode = c.lmode
                and k.request = c.request
                order by object;
                • 5. Re: Object locked in v$locked_object but can't find session/process blocking
                  DroidDr
                  No rows returned by your query. Thanks.
                  • 6. Re: Object locked in v$locked_object but can't find session/process blocking
                    kuljeet singh -
                    I get nothing from
                    select * from dba_waiters;
                    select * from dba_blockers;
                    possible as object are locked by session and no other user required waiting for lock on these object .

                    if these session not required then kill it by alter system kill session 'sid,'serial#' immediate; and release lock;
                    • 7. Re: Object locked in v$locked_object but can't find session/process blocking
                      DroidDr
                      Thank you for your reply.

                      I do not think I can kill the session (514) it does not exist, even though v$locked_object reports tables are locked by session 514

                      see below
                      ----------------------------------------------------------------------
                      select * from v$locked_object
                      
                      XIDUSN                 XIDSLOT                XIDSQN                 OBJECT_ID              SESSION_ID             ORACLE_USERNAME                OS_USER_NAME                   PROCESS      LOCKED_MODE            
                      ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ------------------------------ ------------------------------ ------------ ---------------------- 
                      9                      26                     351467                 373122                 514                                                                                               3                      
                      10                     41                     339655                 373025                 514                                                                                               3                      
                      11                     6                      92004                  373055                 514                                                                                               3                      
                      
                      ----------------------------------------------------------------------
                      select * from v$session order by sid
                      ( no session 514)
                      
                      SADDR            SID                    SERIAL#                AUDSID                 PADDR            USER#                  USERNAME                       COMMAND                OWNERID                TADDR            LOCKWAIT         STATUS   SERVER    SCHEMA#                SCHEMANAME                     OSUSER                         PROCESS      MACHINE                                                          PORT                   TERMINAL         PROGRAM                                                          TYPE       SQL_ADDRESS      SQL_HASH_VALUE         SQL_ID        SQL_CHILD_NUMBER       PREV_SQL_ADDR    PREV_HASH_VALUE        PREV_SQL_ID   PREV_CHILD_NUMBER      PLSQL_ENTRY_OBJECT_ID  PLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_ID        PLSQL_SUBPROGRAM_ID    MODULE                                           MODULE_HASH            ACTION                           ACTION_HASH            CLIENT_INFO                                                      FIXED_TABLE_SEQUENCE   ROW_WAIT_OBJ#          ROW_WAIT_FILE#         ROW_WAIT_BLOCK#        ROW_WAIT_ROW#          LOGON_TIME                LAST_CALL_ET           PDML_ENABLED FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER RESOURCE_CONSUMER_GROUP          PDML_STATUS PDDL_STATUS PQ_STATUS CURRENT_QUEUE_DURATION CLIENT_IDENTIFIER                                                BLOCKING_SESSION_STATUS BLOCKING_INSTANCE      BLOCKING_SESSION       SEQ#                   EVENT#                 EVENT                                                            P1TEXT                                                           P1                     P1RAW            P2TEXT                                                           P2                     P2RAW            P3TEXT                                                           P3                     P3RAW            WAIT_CLASS_ID          WAIT_CLASS#            WAIT_CLASS                                                       WAIT_TIME              SECONDS_IN_WAIT        STATE               SERVICE_NAME                                                     SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS ECID                                                             
                      ---------------- ---------------------- ---------------------- ---------------------- ---------------- ---------------------- ------------------------------ ---------------------- ---------------------- ---------------- ---------------- -------- --------- ---------------------- ------------------------------ ------------------------------ ------------ ---------------------------------------------------------------- ---------------------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------- ------------- ---------------------- ---------------- ---------------------- ------------- ---------------------- ---------------------- ------------------------- ---------------------- ---------------------- ------------------------------------------------ ---------------------- -------------------------------- ---------------------- ---------------------------------------------------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ------------------------- ---------------------- ------------ ------------- --------------- ----------- -------------------------------- ----------- ----------- --------- ---------------------- ---------------------------------------------------------------- ----------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------- ---------------- ---------------------------------------------------------------- ---------------------- ---------------- ---------------------------------------------------------------- ---------------------- ---------------- ---------------------- ---------------------- ---------------------------------------------------------------- ---------------------- ---------------------- ------------------- ---------------------------------------------------------------- --------- --------------- --------------- ---------------------------------------------------------------- 
                      582                  41322092               000007FF3E6279B0 88                     IRMFIN                         0                      2147483644                                               INACTIVE NONE      88                     IRMFIN                         IRM-NTSERVER\ttoupet           2768:5812    IRM-NTSERVER\IRM-WINDEV                                          2091                   IRM-WINDEV       WDMAP.EXE                                                        USER       00               0                                                           000007FF296D46B0 1249880231             gkm8w8157za57 0                                                                                                                     WDMAP.EXE                                        3924246850                                              0                                                                                       4272871                480874                 5                      109733                 0                      2012/09/06 08:54:26       369                    NO           NONE          NONE            NO                                           DISABLED    ENABLED     ENABLED   0                                                                                       NO HOLDER                                                             55                     263                    SQL*Net message from client                                      driver id                                                        1297371904             000000004D545300 #bytes                                                           1                      0000000000000001                                                                  0                      00               2723168908             6                      Idle                                                             0                      369                    WAITING             irtrcs01.ivacorm.com                                             DISABLED  FALSE           FALSE                                                                            
                      000007FF3E1A0FA0 508                    55443                  41321967               000007FF3E62E488 93                     RODMILL                        0                      2147483644                                               INACTIVE DEDICATED 93                     RODMILL                        flong                          4144         IT011                                                            49450                  unknown          SQL Developer                                                    USER       00               0                                                           000007FF126A6BC0 293046999              7jjvy5s8rg2qr 0                                                                                                                     SQL Developer                                    1012150930                                              0                                                                                       4280261                373579                 5                      348686                 0                      2012/09/06 08:39:23       218                    NO           NONE          NONE            NO                                           DISABLED    ENABLED     ENABLED   0                                                                                       NO HOLDER                                                             5486                   263                    SQL*Net message from client                                      driver id                                                        675562835              0000000028444553 #bytes                                                           1                      0000000000000001                                                                  0                      00               2723168908             6                      Idle                                                             0                      218                    WAITING             irtrcs01.ivacorm.com                                             DISABLED  FALSE           FALSE                                                                            
                      000007FF3E1A2518 509                    3086                   41287084               000007FF3E628A20 64                     SYSMAN                         47                     2147483644             000007FF3D7C21D8                  ACTIVE   SHARED    64                     SYSMAN                                                        1234         IRMFAX                                                           1341                                    OMS                                                              USER       000007FF357CE418 4281219134             2b064ybzkwf1y 0                      000007FF357A8BF0 2532399038             5dwsqwabg2pxy 0                      178612                 8                                                                       OEM.SystemPool                                   2960518376             NotificationMgr                  3664650334             IRMFAX.ivacorm.com:4889_Management_Service                       4281687                177737                 14                     40683                  0                      2012/09/03 11:44:37       7                      NO           NONE          NONE            NO                                           DISABLED    ENABLED     ENABLED   0                                                                                       UNKNOWN                                                               48888                  45                     wait for unread message on broadcast channel                     channel context                                                  8792836163648          000007FF3DE04840 channel handle                                                   8792836060256          000007FF3DDEB460                                                                  0                      00               2723168908             6                      Idle                                                             0                      7                      WAITING             irtrcs01.ivacorm.com                                             DISABLED  FALSE           FALSE                                                                            
                      000007FF3E1A3A90 510                    35277                  41286699               000007FF3E6279B0 64                     SYSMAN                         0                      2147483644                                               INACTIVE NONE      64                     SYSMAN                                                        1234         IRMFAX                                                           3154                                    OMS                                                              USER       00               0                                                           000007FF3E844AD8 3275117642             43c5ykm1mcp2a 1                                                                                                                     OEM.CacheModeWaitPool                            796036576                                               0                      IRMFAX.ivacorm.com:4889_Management_Service                       4280509                -1                     0                      0                      0                      2012/09/03 10:58:32       169                    NO           NONE          NONE            NO                                           DISABLED    ENABLED     ENABLED   0                                                                                       NO HOLDER                                                             29909                  263                    SQL*Net message from client                                      driver id                                                        1297371904             000000004D545300 #bytes                                                           1                      0000000000000001                                                                  0                      00               2723168908             6                      Idle                                                             0                      169                    WAITING             irtrcs01.ivacorm.com                                             DISABLED  FALSE           FALSE                                                                            
                      000007FF3E1A5008 511                    51686                  41287085               000007FF3E6279B0 64                     SYSMAN                         0                      2147483644                                               INACTIVE NONE      64                     SYSMAN                                                        1234         IRMFAX                                                           1342                                    OMS                                                              USER       00               0                                                           000007FF36C3CED8 2539923080             6v7n0y2bq89n8 0                                                                                                                     OEM.SystemPool                                   2960518376             JobDispatcher                    875884737              IRMFAX.ivacorm.com:4889_Management_Service                       4281706                -1                     0                      0                      0                      2012/09/03 11:44:37       5                      NO           NONE          NONE            NO                                           DISABLED    ENABLED     ENABLED   0                                                                                       NO HOLDER                                                             38929                  263                    SQL*Net message from client                                      driver id                                                        1297371904             000000004D545300 #bytes                                                           1                      0000000000000001                                                                  0                      00               2723168908             6                      Idle                                                             0                      5                      WAITING             irtrcs01.ivacorm.com                                             DISABLED  FALSE           FALSE                                                                            
                      000007FF3E1A6580 512                    28994                  41293063               000007FF3E6279B0 64                     SYSMAN                         0                      2147483644                                               INACTIVE NONE      64                     SYSMAN                                                        1234         IRMFAX                                                           3636                                    OMS                                                              USER       00               0                                                           000007FF32990C90 1763828656             44nz3b1nk3sxh 1                                                                                                                     OEM.CacheModeWaitPool                            796036576                                               0                      IRMFAX.ivacorm.com:4889_Management_Service                       4280509                -1                     0                      0                      0                      2012/09/04 00:00:02       169                    NO           NONE          NONE            NO                                           DISABLED    ENABLED     ENABLED   0                                                                                       NO HOLDER                                                             20902                  263                    SQL*Net message from client                                      driver id                                                        1297371904             000000004D545300 #bytes                                                           1                      0000000000000001                                                                  0                      00               2723168908             6                      Idle                                                             0                      169                    WAITING             irtrcs01.ivacorm.com                                             DISABLED  FALSE           FALSE                                                                            
                      000007FF3E1ABB60 516                    48427                  41292026               000007FF3E62DC50 93                     RODMILL                        0                      2147483644                                               INACTIVE DEDICATED 93                     RODMILL                        ebesner                        10808        Fingers                                                          59004                  unknown          SQL Developer                                                    USER       00               0                                                           000007FF127B0A58 351849430              bkcpdnnagjkyq 0                                                                                                                     SQL Developer                                    1012150930                                              0                                                                                       4049825                533716                 21                     17036                  0                      2012/09/03 21:53:47       61389                  NO           NONE          NONE            NO                                           DISABLED    ENABLED     ENABLED   0                                                                                       NO HOLDER                                                             2578                   263                    SQL*Net message from client                                      driver id                                                        675562835              0000000028444553 #bytes                                                           1                      0000000000000001                                                                  0                      00               2723168908             6                      Idle                                                             0                      61389                  WAITING             irtrcs01.ivacorm.com                                             DISABLED  FALSE           FALSE                                                                            
                      000007FF3E1AD0D8 517                    2                      0                      000007FF3E635FD0 0                                                     0                      2147483644                                               ACTIVE   DEDICATED 0                      SYS                            SYSTEM                         4292         CHARLIE                                                          0                      CHARLIE          ORACLE.EXE (q001)                                                BACKGROUND 00               0                                    0                      00               0                                    0                                                                                                                                                                      0                                                       0                                                                                       112                    -1                     0                      0                      0                      2012/08/22 17:46:54       1264692                NO           NONE          NONE            NO                                           DISABLED    ENABLED     ENABLED   0                                                                                       UNKNOWN                                                               2685                   281                    Streams AQ: qmn slave idle wait                                                                                                   0                      00                                                                                0                      00                                                                                0                      00               2723168908             6                      Idle                                                             0                      16                     WAITING             SYS$BACKGROUND                                                   DISABLED  FALSE           FALSE                                                                            
                      000007FF3E1AE650 518                    2                      4294967295             000007FF3E635798 0                      SYS                            3                      2147483644                                               INACTIVE DEDICATED 0                      SYS                            IRM-NTSERVER\cluster           3704:1572    IRM-NTSERVER\CHARLIE                                             49181                  CHARLIE          rhs.exe                                                          USER       000007FF3EA55E08 654885888              bcfjz80mhjj00 0                      000007FF3EA55E08 654885888              bcfjz80mhjj00 0                                                                                                                     rhs.exe                                          1733424889                                              0                                                                                       4281686                -1                     0                      0                      0                      2012/08/22 17:46:54       16                     NO           NONE          NONE            NO                                           DISABLED    ENABLED     ENABLED   0                                                                                       NO HOLDER                                                             58582                  263                    SQL*Net message from client                                      driver id                                                        1413697536             0000000054435000 #bytes                                                           1                      0000000000000001                                                                  0                      00               2723168908             6                      Idle                                                             0                      16                     WAITING             SYS$USERS                                                        DISABLED  FALSE           FALSE                                                                            
                      • 8. Re: Object locked in v$locked_object but can't find session/process blocking
                        kuljeet singh -
                        I do not think I can kill the session (514) it does not exist, even though v$locked_object reports tables are locked by session 514
                        could be orphan as unable see any process id name in above output of v$locked_object
                        can you try to manually lock this table and see what happen
                        like
                        LOCK TABLE scott.emp in ROW EXCLUSIVE mode;

                        perform rollback to release it;
                        • 9. Re: Object locked in v$locked_object but can't find session/process blocking
                          DroidDr
                          Hi,

                          After putting the lock on I get
                          OBJECT_ID              SESSION_ID             ORACLE_USERNAME                PROCESS      LOCK_MODE           OWNER                          OBJECT_NAME                                                                                                                      OBJECT_TYPE         SADDR            SERIAL#                PADDR            USERNAME                       OWNERID                STATUS   SERVER    SCHEMANAME                     OSUSER                         MACHINE                                                          PROGRAM                                                          TYPE       LOGON_TIME                LAST_CALL_ET           BLOCKING_SESSION_STATUS EVENT#                 EVENT                                                            WAIT_CLASS#            WAIT_CLASS                                                       WAIT_TIME              SECONDS_IN_WAIT        STATE               
                          ---------------------- ---------------------- ------------------------------ ------------ ------------------- ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- ---------------- ---------------------- ---------------- ------------------------------ ---------------------- -------- --------- ------------------------------ ------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ------------------------- ---------------------- ----------------------- ---------------------- ---------------------------------------------------------------- ---------------------- ---------------------------------------------------------------- ---------------------- ---------------------- ------------------- 
                          373122                 514                                                                ROW EXCLUSIVE       IRMFIN                         IC_PRODUCT_STATUS                                                                                                                TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                          373122                 483                    SYSTEM                         5588         ROW EXCLUSIVE       IRMFIN                         IC_PRODUCT_STATUS                                                                                                                TABLE               000007FF3E17F6E8 2580                   000007FF3E633EF0 SYSTEM                         2147483644             ACTIVE   DEDICATED SYSTEM                         acarrier                       Droid-PC                                                         SQL Developer                                                    USER       2012/09/05 09:44:38       0                      NOT IN WAIT             259                    SQL*Net message to client                                        7                      Network                                                          -1                     0                      WAITED SHORT TIME   
                          373025                 514                                                                ROW EXCLUSIVE       IRMFIN                         IC_BIN_DETAIL_STATUS                                                                                                             TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                          373055                 514                                                                ROW EXCLUSIVE       IRMFIN                         IC_LOT_STATUS                                                                                                                    TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                          
                          after rollback 
                          OBJECT_ID              SESSION_ID             ORACLE_USERNAME                PROCESS      LOCK_MODE           OWNER                          OBJECT_NAME                                                                                                                      OBJECT_TYPE         SADDR            SERIAL#                PADDR            USERNAME                       OWNERID                STATUS   SERVER    SCHEMANAME                     OSUSER                         MACHINE                                                          PROGRAM                                                          TYPE       LOGON_TIME                LAST_CALL_ET           BLOCKING_SESSION_STATUS EVENT#                 EVENT                                                            WAIT_CLASS#            WAIT_CLASS                                                       WAIT_TIME              SECONDS_IN_WAIT        STATE               
                          ---------------------- ---------------------- ------------------------------ ------------ ------------------- ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- ---------------- ---------------------- ---------------- ------------------------------ ---------------------- -------- --------- ------------------------------ ------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ------------------------- ---------------------- ----------------------- ---------------------- ---------------------------------------------------------------- ---------------------- ---------------------------------------------------------------- ---------------------- ---------------------- ------------------- 
                          373122                 514                                                                ROW EXCLUSIVE       IRMFIN                         IC_PRODUCT_STATUS                                                                                                                TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                          373025                 514                                                                ROW EXCLUSIVE       IRMFIN                         IC_BIN_DETAIL_STATUS                                                                                                             TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                          373055                 514                                                                ROW EXCLUSIVE       IRMFIN                         IC_LOT_STATUS                                                                                                                    TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                          I see my new lock. When I rollback, my new lock is gone, but the others are still there.

                          Thank you.
                          • 10. Re: Object locked in v$locked_object but can't find session/process blocking
                            kuljeet singh -
                            I see my new lock. When I rollback, my new lock is gone, but the others are still there.
                            let it be there,looks orphan and it appear this will not make any problem new transaction.
                            will be auto clear in next reboot.
                            • 11. Re: Object locked in v$locked_object but can't find session/process blocking
                              DroidDr
                              Thank you,

                              The orphan locks do lock SOME records, so those records are not available to be modified. This is the problem here.

                              If I do:


                              select * from irmfin.IC_PRODUCT_STATUS for update nowait;
                              I get:

                              Error starting at line 214 in command:
                              select * from irmfin.IC_PRODUCT_STATUS for update nowait
                              Error report:
                              SQL Error: ORA-00054: resource busy and acquire with NOWAIT specified
                              00054. 00000 - "resource busy and acquire with NOWAIT specified"
                              *Cause:    Resource interested is busy.
                              *Action:   Retry if necessary.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                              • 12. Re: Object locked in v$locked_object but can't find session/process blocking
                                jgarry
                                I think you might have to integrate the query from the very old MOS Killing Session does not release locks [ID 233884.1] with your locked object session query to find a thread id to kill with orakill.
                                select p.spid "OS Thread", b.name "Name-User", s.osuser, s.
                                program
                                   from v$process p, v$session s, v$bgprocess b
                                      where p.addr = s.paddr
                                      and p.addr = b.paddr UNION ALL
                                          select p.spid "OS Thread", s.username "Name-User",
                                 s.osuser, s.program
                                             from v$process p, v$session s
                                                where p.addr = s.paddr
                                                and s.username is not null
                                /
                                • 13. Re: Object locked in v$locked_object but can't find session/process blocking
                                  DroidDr
                                  Thanks,

                                  The problem session is not in V$session so the query you provided does not show that session unfortunately.

                                  THanks for the help though, it is appreciated.
                                  • 14. Re: Object locked in v$locked_object but can't find session/process blocking
                                    jgarry
                                    Are you running iRenaissance?

                                    I think you need to use Oracle support on this one, after making a replicable case and grabbing the SQL.

                                    Can you run a 10704 trace? (Google for details)
                                    1 2 Previous Next