1 以 oracle 用户登录 XXXX 主机
2 登录目标数据库
exoport ORACLE_SID = SID --根据实际情况指定
sqlplus /nolog
conn / as sysdba
查看阻塞的会话
select sid, serial#, blocking_instance, blocking_session from gv$session t
where blocking_session is not null;
查看有死锁的会话
select sn.sid, sn.serial#, sn.username, sn.machine, sn.terminal, sn.program, sn.type
from v$session sn
where sn.sid in (select lk.sid from v$lock lk);
查出锁定object的session的信息以及被锁定的object名
col oracle_username for a20
col os_user_name for a20
col machine for a20
col terminal for a20
col object_name for a20
col logon_time for a25
select lk.session_id sid, s.serial#, lk.locked_mode,lk.oracle_username,
lk.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
from v$locked_object lk, all_objects o, v$session s
where lk.object_id = o.object_id
and lk.session_id = s.sid
order by sid, s.serial#;
查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句
col oracle_username for a20
col os_user_name for a20
col machine for a20
col terminal for a20
col action for a20
select lk.session_id sid, s.serial#, lk.locked_mode, lk.oracle_username, s.user#,
lk.os_user_name,s.machine, s.terminal,a.sql_text, a.action
from v$sqlarea a,v$session s, v$locked_object lk
where lk.session_id = s.sid
and s.prev_sql_addr = a.address
order by sid, s.serial#;
查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
select s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, lk.type
from v$session s, v$lock lk
where s.sid = lk.sid
and s.username is not null
order by sid;
生成杀会话的语句
select ‘alter system kill session ‘’’ || sid ||’,’ || SERIAL# ||’’’;’ from v$session where sid=&sid;
生成杀进程的语句
select 'kill -9 '||spid from v$process where addr = (select paddr from v$session where sid=&sid);
