Select V$Lock.Sid,
       Decode(V$Lock.Type,'MR', 'Media Recovery',
                          'RT', 'Redo Thread',
                          'UN', 'User Name',
                          'TX', 'Transaction',
                          'TM', 'DML',
                          'UL', 'PL/SQL User Lock',
                          'DX', 'Distributed Xaction',
                          'CF', 'Control File',
                          'IS', 'Instance State',
                          'FS', 'File Set',
                          'IR', 'Instance Recovery',
                          'ST', 'Disk Space Transaction',
                          'TS', 'Temp Segment',
                          'IV', 'Library Cache Invalida-tion',
                          'LS', 'Log Start or Switch',
                          'RW', 'Row Wait',
                          'SQ', 'Sequence Number',
                          'TE', 'Extend Table',
                          'TT', 'Temp Table',
                                'Unknown') LockType,
       Rtrim(object_type) Object_Type ,
       Rtrim(Owner) Owner ,
       Object_Name,
       Decode(Lmode, 0, 'None',
                     1, 'Null',
                     2, 'Row-S',
                     3, 'Row-X',
                     4, 'Share',
                     5, 'S/Row-X',
                     6, 'Exclusive',
                        'Unknown') LockMode,
       (Select Decode(Sum(Request), 0, 'None',
                                    1, 'Null',
                                    2, 'Row-S',
                                    3, 'Row-X',
                                    4, 'Share',
                                    5, 'S/Row-X',
                                    6, 'Exclusive',
                                    'Unknown') From V$Lock B Where B.Sid = V$Lock.Sid)  RequestMode,
       Ctime,
       (Select Decode(Sum(Block),0,'Noblocker',1,'Blocker','Unknow') From V$Lock A Where A.Sid = V$Lock.Sid)  Block,
       'Alter System Kill Session ''' || V$Lock.Sid || ',' || V$Session.Serial# || ''';' Kill_Session,
       (Select V$Sql.Sql_Text From V$Sql Where V$Sql.Address = V$Session.Sql_Address) Sql_Text,
       V$Session.Machine  Machine,
       V$Session.Terminal Terminal
  From V$Lock, All_Objects,V$Session
 Where V$Lock.Id1 = All_Objects.Object_Id And V$Lock.Sid = V$Session.Sid;