Wednesday, August 6, 2008

Get detail of Locks with Object Locked

To get detail of Locks with Object Locked:-
------------------------------------------------

If you want to know the objects which are locked in your database, then you can use the following query.

SELECT VLO.OS_USER_NAME "OS USERNAME", VLO.ORACLE_USERNAME "DB USER",
VP.SPID "SPID", AO.OWNER "OWNER", AO.OBJECT_NAME "OBJECT LOCKED",AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE,
1, 'NO LOCK',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCL',
6, 'EXCLUSIVE',
NULL
) "MODE OF LOCK",
VS.STATUS "CURRENT STATUS"
FROM V$LOCKED_OBJECT VLO, ALL_OBJECTS AO, V$SESSION VS, V$PROCESS VP
WHERE VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> 'KILLED'
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;
at

3 comments:

  1. phaniApril 30, 2009 at 10:18 AM

    Hi,
    Following queries can be used to
    kill any job.

    SELECT * FROM DBA_DDL_LOCKS WHERE NAME LIKE'PACKAGE NAME ON WHICH CONC
    PGM IS RUNNING%';

    -- TAKE SESSION_ID AS SID

    SELECT * FROM V$SESSION WHERE SID=----;

    --SELECT SID AND SERIAL# FROM THE ABOVE


    ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

    Regards,
    Phani

    ReplyDelete
  2. phaniFebruary 9, 2010 at 4:49 AM

    You can following the steps given to below to kill the session.

    SELECT VLO.OS_USER_NAME "OS USERNAME", VLO.ORACLE_USERNAME "DB USER", vs.SID,
    VP.SPID "SPID", AO.OWNER "OWNER", AO.OBJECT_NAME "OBJECT LOCKED",AO.OBJECT_TYPE,
    DECODE (VLO.LOCKED_MODE,
    1, 'NO LOCK',
    2, 'ROW SHARE',
    3, 'ROW EXCLUSIVE',
    4, 'SHARE',
    5, 'SHARE ROW EXCL',
    6, 'EXCLUSIVE',
    NULL
    ) "MODE OF LOCK",
    VS.STATUS "CURRENT STATUS"
    FROM V$LOCKED_OBJECT VLO, ALL_OBJECTS AO, V$SESSION VS, V$PROCESS VP
    WHERE VLO.OBJECT_ID = AO.OBJECT_ID
    AND VS.STATUS <> 'KILLED'
    AND VLO.SESSION_ID = VS.SID
    AND VS.PADDR = VP.ADDR;

    From the above Query, take the session ID "vs.SID" value.

    select sid, serial#, command, taddr, 'alter system kill session '||''''||sid||', '||serial#||''''
    from v$session where sid in ('Your SID Value');

    The above query would return some Alter command. Execute same Alter Command to Kill the session.

    Example for the Alter command.

    alter system kill session '31, 4857';

    In the above Alter statement, 31 is the SID Number.

    Thanks and regards,
    Phani

    ReplyDelete
  3. AnonymousJuly 19, 2016 at 10:41 PM

    This comment has been removed by a blog administrator.

    ReplyDelete