oracle查看锁表进程（oracle 查锁表进程）

大家好，我是讯享网，很高兴认识大家。

 <p>select sess.sid, <br />     sess.serial#, <br />     lo.oracle_username, <br />     lo.os_user_name, <br />     ao.object_name, <br />     lo.locked_mode <br />     from v$locked_object lo, <br />     dba_objects ao, <br />     v$session sess <br /> where ao.object_id &#61; lo.object_id and lo.session_id &#61; sess.sid; </p>

讯享网

select sess.sid,
 sess.serial#,
 lo.oracle_username,
 lo.os_user_name,
 ao.object_name,
 lo.locked_mode ,
 ‘alter system kill session ’ || chr(39) || sess.sid || ‘,’ || sess.serial# || chr(39) || ‘;’
 from v\(locked_object lo, dba_objects ao, v\)session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;

查看历史SQL语句：

select * from v\(active_session_history 查询Session中的SQL语句 SELECT VS.SQL_TEXT FROM V\)SQL VS, V\(SESSION SN WHERE VS.SQL_ID = SN.SQL_ID AND SN.SID = &SESSION_ID; 查询锁表、阻塞的SQL语句： select s.MACHINE, s.PROGRAM, s.event, s.STATUS, q.sql_text from gv\)session s, gv\(sql q where sid in (select distinct sid from gv\)lock
 where type in (‘TX’, ‘TM’)
 and inst_id = s.inst_id)
 and nvl(s.sql_id, s.prev_sql_id) = q.sql_id
 and s.inst_id = q.inst_id;

SELECT ‘alter system kill session ’ || chr(39) || SID || ‘,’ || SERIAL# || chr(39) || ‘;’SID, SERIAL# FROM GV\(SESSION V WHERE SID IN (SELECT SID FROM GV\)LOCK L, DBA_OBJECTS O
WHERE L.ID1 = O.OBJECT_ID AND O.OBJECT_NAME =UPPER(‘INTE_FIN_SUB’) ); —ALTER SYSTEM KILL SESSION ‘20,2661’;

–查询使用某个表的进程：
SELECT SID, SERIAL# FROM GV\(SESSION V WHERE SID IN (SELECT SID FROM GV\)LOCK L, DBA_OBJECTS O
WHERE L.ID1 = O.OBJECT_ID AND O.OBJECT_NAME =UPPER(‘TEMP_FSF_GJXX’) );

kill rac模式上的实例进程
select * from DBA_OBJECTS O WHERE O.OBJECT_NAME =UPPER(‘INTE_FIN_SUB’);
select * from GV\(LOCK L where L.ID1 in (select O.OBJECT_ID from DBA_OBJECTS O WHERE O.OBJECT_NAME =UPPER('INTE_FIN_SUB')); select * from GV\)SESSION V where V.SID in (‘2138’,‘1496’,‘365’);
alter system kill session ‘365,58073,@2’ immediate;
alter system kill session ‘1496,133,@2’ immediate;
alter system kill session ‘2138,4769,@2’ immediate;

select * from v\(asm_diskgroup SELECT OBJECT_NAME, MACHINE, S.SID, S.SERIAL# FROM GV\)LOCKED_OBJECT L, DBA_OBJECTS O, GV\(SESSION S WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID; 1.查询数据库中的锁 select * from v\)lock;
select * from v\(lock where block=1; 2.查询被锁的对象 select * from v\)locked_object;

查被阻塞的会话
select * from v\(lock where lmode=0 and type in ('TM','TX'); 查阻塞别的会话锁 select * from v\)lock where lmode>0 and type in (‘TM’,‘TX’);

select * from v\(session where lockwait is not null; 5.查询会话之间锁等待的关系 select a.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from v\)lock a,v\(lock b where a.id1=b.id1 and a.id2=b.id2 and a.block=1 and b.block=0; 6.查询锁等待事件 select * from v\)session_wait where event=‘enqueue’;
7.查找锁住的表和解锁
select b.owner TABLEOWNER, b.object_name TABLENAME, c.OSUSER LOCKBY, c.USERNAME LOGINID, c.sid SID, c.SERIAL# SERIAL
from v\(locked_object a,dba_objects b, v\)session c
where b.object_id = a.object_id AND a.SESSION_ID =c.sid;
–通过SID, SERIAL解锁
–alter system kill session ‘SID, SERIAL’;

讯享网

查看锁表进程SQL语句2：
select * from v\(session t1, v\)locked_object t2 where t1.sid = t2.SESSION_ID;

杀掉锁表进程：
如有記錄則表示有lock，記錄下SID和serial# ，將記錄的ID替換下面的738,1429，即可解除LOCK
alter system kill session ‘738,1429’;

步骤1：用以下SQL查看进程列表，判断出被锁定的表

SELECT dob.OBJECT_NAME Table_Name,lo.SESSION_ID||‘, ’||vss.SERIAL# 删除号,
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 ,vss.*
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 2,3,DOB.object_name --查找被锁表 步骤2 删除进程，如之前的“删除号”查找出的结果为“286, 2184”，则运行以下SQL ALTER system kill session '286, 2184' --Oracle中删除正在连接的用户 select s.username,s.osuser,s.program,s.MACHINE, s.sid, s.serial#, p.spid, 'alter system kill session '||''''||trim(s.sid)||','||trim(s.serial#)||''';' from v\)session s,v\(process p where s.paddr = p.addr and s.username is not null; 今天在导一个临时表的数据，导出完成后准备清空数据，执行truncate命令时，遇到如下问题： ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效 解决方法如下： ========================================================= SQL> select session_id from v\)locked_object;

SESSION_ID
———-
56

SID SERIAL# USERNAME OSUSER
———- ———- —————————— ——————————
56 2088 ghb fy

执行完上述命令后，提示会话断开。重新连接数据库，然后执行truncate操作，成功！
————————————————
版权声明：本文为CSDN博主「baiwuzhilian2008」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/baiwuzhilian2008/article/details/

oracle查看锁表进程（oracle 查锁表进程）

相关推荐