查看被锁的表及sid :

SELECT /*+ rule*/
a.sid, b.owner, object_name, object_type
FROM v$lock a, all_objects b
WHERE TYPE = 'TM'
and a.id1 = b.object_id;


   SID OWNER                          OBJECT_NAME                    OBJECT_TYPE
---------- ----------------- ------------------------------ -------------------
       159 TEST                           D                              TABLE

 

 

还通过sid 查到SERIAL#:

SQL> select sid,serial#,username from v$session where sid=159;

 查看当前用户连接情况:

SQL> select sid,serial#,username from v$session;
  SID    SERIAL# USERNAME
---------- ---------- ----------------------------------------------------------
        15       1712 SYS
        19      14756 MAMIAI
        22       4038 MAMIAI
        30       8187 MAMIAI
        38       2017 MAMIAI
        71       8001 MAMIAI
        77      26252 MAMIAI                  #########kill
        88       1051 MAMIAI
        89      40939 MAMIAI
       115      12025 MAMIAI
       126       4456 MAMIAI
把某个连(77      26252 MAMIAI)接踢出去:

SQL> alter system kill session '77,26252';
 
格式为 alter system kill session 'sid,serial'; 

 #########################################################################

 --查询出锁表的会话

select a.owner,

       a.object_name,

       a.subobject_name,

       a.object_type,

       l.session_id,

       l.oracle_username,

       l.os_user_name,

       se.sid,

       se.serial#

  from  dba_objects a, v$locked_object l,v$session se

where a.object_id = l.object_id

       and se.osuser = l.os_user_name

 

--杀session,可以解锁表的功能

alter system kill session 'sid,serial#';