Locks on Oracle Database

Below are the steps that I have done.

1.       Found out locks on database using below query:

SET LINESIZE 145

SET PAGESIZE 66

COLUMN owner        FORMAT a5      HEADING 'Owner'

COLUMN object_type  FORMAT a10     HEADING 'Type'

COLUMN object_name  FORMAT a25     HEADING 'Name'

COLUMN Object       FORMAT a27     HEADING 'Object'

COLUMN locked_mode  FORMAT a20     HEADING 'Locked Mode'

COLUMN sid          FORMAT 999999  HEADING 'DB SID'

COLUMN username     FORMAT a15     HEADING 'Locker'

COLUMN osuser       FORMAT a10     HEADING 'O/S User'

COLUMN logon_time                  HEADING 'Login Time'

prompt

Prompt +-----------------------------------------------------------------+

Prompt | Table Locking Info FROM v$locked_object, dba_objects, v$session |

Prompt +-----------------------------------------------------------------+

SELECT

      SUBSTR(b.owner, 1, 8)||'.'||SUBSTR(b.object_name, 1, 18) "Object"

  , b.object_type                   object_type

  , DECODE(a.locked_mode

             , 0, 'NONE'

             , 1, 'NULL'

             , 2, 'ROW SHARE'

             , 3, 'ROW EXCLUSIVE'

             , 4, 'SHARE'

             , 5, 'SHARE ROW EXCLUSIVE'

             , 6, 'EXCLUSIVE')      locked_mode

  , a.session_id                    "DB Sid"

  ,a.oracle_username                "Locker"

  , a.os_user_name                  osuser

  , TO_CHAR(c.logon_time,'YYYY/MM/DD HH24:MI:SS') logon_time

FROM

    v$locked_object a

  , dba_objects b

  , v$session c

WHERE

      a.object_id  = b.object_id

  AND a.session_id = c.sid

ORDER BY

    b.owner

  , b.object_type

  , b.object_name

/

2.       Found out the session details from v$session  for the SID that corresponds to the output of step1.

Or you can even directly get the details from below query.

                

select sid,serail#, username,schemaname,osuser,logon_time,status from v$session where SID IN (select session_id from dba_dml_locks);

3.       Killed those sessions using below query after getting proper approvals.

ALTER SYSTEM KILL SESSION 'sid,serial#';