工具: oracle client :sqlplus
oracle server
os command: netstat
oracle dynamic views:
v$session,v$transaction,v$process,v$sql.
2.select sid,username,status from v$session;
status: active表示正在执行sql语句。inactive没有执行sql.
3.抓取client端和oracle服务器端的ip,port,pid信息。
netstat -naop
4.1)select addr,ses_addr from v$transaction;
2)select saddr,sid,username,status,paddr from v$session where saddr=ses_addr;
3)select addr,spid from v$process where addr=paddr;
4)ps -ef|grep spid
找到那个transaction与进程id的关联。
5)select sid,prev_sql_addr,username,status from v$session where PADDR=addr;
6)select sql_text,address from v$sql where address=prev_sql_addr;
5.select addr,sid,username,s.status,process,program from v$transaction t,v$session s where t.ses_addr=s.saddr
process 链接到oracle 服务器的client的进程id; program 链接到oracle 服务器的client的进程程序名。
6. LOCKWAIT:锁定地址select
saddr,sid,username,status,paddr,s.LOCKWAIT,s.OSUSER,s.MACHINE,s.PROGRAM,s.LOCKWAIT,s.COMMAND
from v$session s where saddr=ses_addr;
找到锁select * from v$lock l where l.kADDR=LOCKWAIT
7.查看那个表被锁了select a.object_name,a.object_type,s.osuser,s.username,s.status,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s ,all_objects a
where l.object_id = o.object_id and l.session_id=s.sid and a.object_id=l.OBJECT_ID
8.kill 死锁的sessionselect addr,ses_addr from v$transaction;
select sid,s.SERIAL# from v$session s where saddr=ses_addr;
alter system kill session 'sid,serial#';
原文出处:http://dolphin-ygj.iteye.com/blog/450381