DB lock in Websphere commerce

Check what all tables are locked

SET LINESIZE 500

SET PAGESIZE 1000

SET VERIFY OFF

COLUMN owner FORMAT A20

COLUMN username FORMAT A20

COLUMN object_owner FORMAT A20

COLUMN object_name FORMAT A30

COLUMN locked_mode FORMAT A15

SELECT b.inst_id,

          b.session_id AS sid,

           NVL(b.oracle_username, '(oracle)') AS username,

           a.owner AS object_owner,

           a.object_name,

           Decode(b.locked_mode, 0, 'None',

                             1, 'Null (NULL)',

                                     2, 'Row-S (SS)',

                                 3, 'Row-X (SX)',

                                4, 'Share (S)',

                             5, 'S/Row-X (SSX)',

                                   6, 'Exclusive (X)',

                                b.locked_mode) locked_mode,

          b.os_user_name

   FROM   dba_objects a,

          gv$locked_object b

WHERE  a.object_id = b.object_id

ORDER BY 1, 2, 3, 4;

Find the locked DB sessions

SELECT 'ALTER SYSTEM KILL SESSION "' ||  SID || ',' || SERIAL#  ||'";'

FROM

  (SELECT SID, SERIAL# FROM V$SESSION WHERE SID IN

      (SELECT UNIQUE(b.session_id)

          

      FROM   dba_objects a,

           gv$locked_object b

      WHERE  a.object_id = b.object_id) 

  )

Clear The lock

Execute the outcome of the above select query after replacing double quotes with single as below.

ALTER SYSTEM KILL SESSION '17,10721';