ORACLE 锁表处理小记

查找并KILL

SELECT distinct a_s.owner,

  a_s.object_name,
  a_s.object_type,
  VN.SID,
  VN.SERIAL#,
  VS.SPID "OS_PID",
  VN.PROCESS "CLIENT_PID",
  VN.USERNAME,
  VN.OSUSER,
  VN.MACHINE "HOSTNAME" ,
  VN.TERMINAL,
  VN.PROGRAM,
  TO_CHAR(VN.LOGON_TIME,'YYYY-MM-DD HH24:MI:SS')"LOGIN_TIME",
  'alter system kill session '''||vn.sid||','||vn.serial#||''';' "ORACKE_KILL",
  'kill -9 '|| VS.SPID "OS_KILL"
FROM ALL_OBJECTS A_S,
  V$LOCKED_OBJECT V_T,
  V$SESSION VN,
  V$PROCESS VS
WHERE A_S.OBJECT_ID=V_T.OBJECT_ID
AND V_T.SESSION_ID =VN.SID
AND VS.ADDR=VN.PADDR
AND VN.USERNAME NOT IN('SYSMAN','SYS');

查看对应的SQL

SELECT sql_id FROM v$session WHERE SID=130 ;

 SELECT * FROM v$sql WHERE sql_id='f88xr14r5z81x';