确实是找不到锁的 不知道为什么 用的是以下sql select object_name as 对象名称,machine as 主机,s.program 程序,s.sid||','||s.serial# as killid, p.spid as OS进程号 from gv$locked_object l, dba_objects o ,gv$session s ,gv$process p where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;
网上看到的一个查未提交的sql 发现只能查出会话当前最后执行的未提交的sql (例如某个会话先插了A表 再插B表 只能查出插B表的sql)select sql.sql_text from v$transaction trans, v$session ses, v$sql sql where trans.SES_ADDR = ses.SADDR and ses.username is not null and ses.prev_sql_addr = sql.address
第三个字段就是被锁的表名: SELECT p.spid , decode(locked_mode,0,'None',1,'Null',2,'Row share', 3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type, c.object_name , b.session_id , b.oracle_username, b.os_user_name, b.locked_mode, a.sid, a.serial#, a.machine, a.action, to_char(a.logon_time,'yyyy/mm/dd hh24:mi') 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
2、估计你的主键生成是 查询主键最大值 然后+1 再处理。建议将主键生成改成 序列的生成方式,或者利用序列生成相应的主键值,问题可以得到根治。
既然是没有提交的sql,那么肯定是可以查找到的阿,查看当前操作的所有会话,其实还是有锁表吧,只不过不是你加的锁而已!
select object_name as 对象名称,machine as 主机,s.program 程序,s.sid||','||s.serial# as killid, p.spid as OS进程号 from gv$locked_object l, dba_objects o ,gv$session s ,gv$process p where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;
from v$transaction trans, v$session ses, v$sql sql
where trans.SES_ADDR = ses.SADDR and ses.username is not null and ses.prev_sql_addr = sql.address
SELECT p.spid ,
decode(locked_mode,0,'None',1,'Null',2,'Row share',
3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type,
c.object_name ,
b.session_id ,
b.oracle_username,
b.os_user_name,
b.locked_mode,
a.sid,
a.serial#,
a.machine,
a.action,
to_char(a.logon_time,'yyyy/mm/dd hh24:mi')
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