查看谁锁表SQL

杀掉查找出的进程必须有sys权限

法一：SELECT "SYS"."V_$LOCKED_OBJECT"."SESSION_ID" ,
           "SYS"."V_$LOCKED_OBJECT"."ORACLE_USERNAME" ,
           "SYS"."V_$LOCKED_OBJECT"."OS_USER_NAME" ,         
           "SYS"."OBJ$"."NAME"    
        FROM "SYS"."V_$LOCKED_OBJECT" ,
             "SYS"."OBJ$"    
        WHERE ( "SYS"."V_$LOCKED_OBJECT"."OBJECT_ID" = "SYS"."OBJ$"."OBJ#" )
        ORDER BY "SYS"."V_$LOCKED_OBJECT"."ORACLE_USERNAME"          ASC ;

---------------------------------------------------------
法二：SELECT substr(to_char(l.sid),1,4) "SID",
        substr(s.type,1,1)          "B/U",
        P.spid                      "SRVR PID",
        s.process                   "CLNT PID",
        substr(s.machine,1,7)       "MACHINE",                     
        l.type,
        DECODE(L.TYPE,'MR','File_ID: '||L.ID1,
                      'TM', LO.NAME,
                      'TX','USN: '||to_char(TRUNC(L.ID1/65536))||' RWO: '||nvl(RWO.NAME,'None'),
               L.ID1)
        LOCK_ID1,
        decode(l.lmode,
        0, 'None',
        1, 'Null',
        2, 'Row-S (SS)',
        3, 'Row-X (SX)',
        4, 'Share',
        5, 'S/Row-X (SSX)',
        6, 'Exclusive',
        substr(to_char(l.lmode),1,13)) "Locked Mode",
        decode(l.request,
        0, 'None',
        1, 'Null',
        2, 'Row-S (SS)',
        3, 'Row-X (SX)',
        4, 'Share',
        5, 'S/Row-X (SSX)',
        6, 'Exclusive',
        substr(to_char(l.request),1,13)) "Requested",
        l.ctime,
        l.block
FROM   v$process P,
        v$session S,
        v$lock l,
        sys.obj$ lo,
        sys.obj$ rwo
WHERE l.type      != 'MR' AND    l.sid = S.sid (+)
AND    S.paddr = P.addr (+)
AND    LO.OBJ#(+) = L.ID1
AND    RWO.OBJ#(+) = S.ROW_WAIT_OBJ#
order by l.sid;