查看表被锁的SQL

--查看哪张表被锁了，是哪个用户和机器锁的，什么时候锁的 
select 
object_name,s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time
from v$locked_object l,dba_objects o ,v$session s
where 
l.object_id　=　o.object_id 
and l.session_id=s.sid;

select p.spid,a.serial#,c.object_name,b.session_id,b.oracle_username,b.os_user_name 
from v$process p,v$session a, v$locked_object b,all_objects c 
where p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id ; 

--查看是哪条SQL引起的死锁
select A.SQL_TEXT, B.USERNAME, C.OBJECT_ID, C.SESSION_ID, 
       B.SERIAL#, C.ORACLE_USERNAME,C.OS_USER_NAME,C.Process,
       ''''||C.Session_ID||','||B.SERIAL#||''''
from v$sql A, v$session B, v$locked_object C
where A.HASH_VALUE = B.SQL_HASH_VALUE and
B.SID = C.Session_ID
and b.SID=3991;

--解锁表
alter system kill session '24,111'; (其中24,111分别是上面查询出的sid,serial#)

如果还不能解决：

select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr;

　　其中sid用死锁的sid替换: exit
ps -ef|grep spid

　　其中spid是这个进程的进程号，kill掉这个Oracle进程