从客户端到SQL语句的跟踪

1.工具： oracle client :sqlplus
         oracle server 9i.
  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 死锁的session

 select addr,ses_addr from v$transaction;
 select sid,s.SERIAL# from v$session s where saddr=ses_addr;
 alter system kill session 'sid,serial#';