最近数据库访问加了一个安全限制,如果有多人多次尝试登录的时候失败,会造成锁表。因此将前辈们的一些解除锁表操作的语句记录了一下,以备后用:
1,查明锁表进程并杀掉
select *
fromv$locked_object a, dba_objects b, v$session c
wherea.OBJECT_ID = b.object_id and a.SESSION_ID = c.SID;
selectusername,sid,serial# from v$session;
altersystem kill session 'sid,serial# ';
altersystem kill session'9,4';
2,被锁的进程较少的时候可以使用上面的方法,如果被锁的进程较多,可以考虑换一个方式。
直接将构造出来的命令放到一个表中
然后粘贴到command 窗口中批量执行即可:
SELECT 'alter system kill session '|| SID||' '||serial#||';'FROM v$session;
3,如果需要查询是哪些主机以及其对应的进程,可以参考如下语句:
SELECT T2.USERNAME,
T2.SID,
T2.SERIAL#,
T3.OBJECT_NAME,
T2.OSUSER,
T2.MACHINE,
T2.PROGRAM,
T2.LOGON_TIME,
T2.COMMAND,
T2.LOCKWAIT,
T2.SADDR,
T2.PADDR,
T2.TADDR,
T2.SQL_ADDRESS,
T1.LOCKED_MODE
FROM V$LOCKED_OBJECT T1, V$SESSION T2,DBA_OBJECTS T3
WHERE T1.SESSION_ID = T2.SID
AND T1.OBJECT_ID = T3.OBJECT_ID
ORDER BY T2.LOGON_TIME;
1,查明锁表进程并杀掉
select *
fromv$locked_object a, dba_objects b, v$session c
wherea.OBJECT_ID = b.object_id and a.SESSION_ID = c.SID;
selectusername,sid,serial# from v$session;
altersystem kill session 'sid,serial# ';
altersystem kill session'9,4';
2,被锁的进程较少的时候可以使用上面的方法,如果被锁的进程较多,可以考虑换一个方式。
直接将构造出来的命令放到一个表中
然后粘贴到command 窗口中批量执行即可:
SELECT 'alter system kill session '|| SID||' '||serial#||';'FROM v$session;
3,如果需要查询是哪些主机以及其对应的进程,可以参考如下语句:
SELECT T2.USERNAME,
T2.SID,
T2.SERIAL#,
T3.OBJECT_NAME,
T2.OSUSER,
T2.MACHINE,
T2.PROGRAM,
T2.LOGON_TIME,
T2.COMMAND,
T2.LOCKWAIT,
T2.SADDR,
T2.PADDR,
T2.TADDR,
T2.SQL_ADDRESS,
T1.LOCKED_MODE
FROM V$LOCKED_OBJECT T1, V$SESSION T2,DBA_OBJECTS T3
WHERE T1.SESSION_ID = T2.SID
AND T1.OBJECT_ID = T3.OBJECT_ID
ORDER BY T2.LOGON_TIME;