SELECT l.session_id sid ,

       substr(o.owner,

              1,

              8) owner,

       o.object_type object_type,

       substr(o.object_name,

              1,

              18) object_name,

       decode(l.locked_mode,

              0,

              'None',

              1,

              'Null',

              2,

              'Row-S',

              3,

              'Row-X',

              4,

              'Share',

              5,

              'S/Row-X',

              6,

              'Exclusive') locked_mode,

       l.oracle_username username,

       l.os_user_name osuser,

       to_char(s.logon_time,

               'YYYY/MM/DD HH24:MI:SS') logon_time,

       decode(s.row_wait_obj#,

              - 1,

              NULL,

              dbms_rowid.rowid_create( 1,

                                      s.row_wait_obj#,

                                      s.row_wait_file#,

                                      s.row_wait_block#,

                                      s.row_wait_row#)) row_id

  FROM gv$locked_object l, --gv$locked_object

       all_objects     o,

       gv$session       s --gv$session

 WHERE l.object_id = o.object_id

   AND l.session_id = s.sid

--AND l.inst_id = s.inst_id

-- AND o.object_name = 'LOCK_TEST';

 

 

--杀死session及进程

SELECT s.sid session_id,

       p.spid os_process_id,

       p.pid oracle_process_id,

       'alter system kill session ''' || to_char(s.sid) || ',' || to_char(s.serial#) || ''' immediate;' kill_db_session,

       'kill -9 ' || p.spid kill_os_session

  FROM v$process p,

       v$session s

 WHERE p.addr = s.paddr

   AND s.sid = &sid;