锁表处理及查询
查看Oracle数据库被锁住的表,删除锁表的进程
1.查看被锁住的表
SELECT dob.object_name table_name, lo.locked_mode, lo.session_id, vss.serial#, vss.action action, vss.osuser osuser, vss.logon_time, vss.process ap_pid, vps.spid db_pid FROM v$locked_object lo, dba_objects dob, v$session vss, v$process vps WHERE lo.object_id = dob.object_id AND lo.session_id = vss.sid AND vss.paddr = vps.addr ORDER BY dob.object_name;
2.删除锁表的进程
alter system kill session 'session_id, serial#';
3.一个帮你写杀进程的语句
SELECT p.spid, o.owner, o.object_name, s.sid, s.serial#, s.username, s.command, s.osuser, s.machine, s.program, s.MODULE, 'alter system kill session ' || '''' || s.sid || ',' || s.serial# || '''' || ';' kill_session FROM all_objects o, v$session s, v$process p, v$locked_object l WHERE l.object_id = o.object_id AND l.session_id = s.sid AND s.paddr = p.addr;
其他SQL:查询系统内Session,可根据用户
SELECT dob.object_name table_name, vss.CLIENT_IDENTIFIER, dob.owner, lo.locked_mode, lo.session_id, vss.serial#, vss.action action, vss.osuser osuser, vss.logon_time, vss.process ap_pid, vps.spid db_pid FROM v$locked_object lo, dba_objects dob, v$session vss, v$process vps WHERE lo.object_id = dob.object_id AND lo.session_id = vss.sid AND vss.paddr = vps.addr -- AND vss.CLIENT_IDENTIFIER like '%sysadmi%' --限制用户 ORDER BY dob.object_name;