oracle鎖表問題
come from:**.com/blog/386765
關鍵字: oracle鎖表問題 1、Select * From v$locked_object;鎖表物件
根據sessionid,到Tools--sessions找到相應的sessions即可看到鎖表的sql語句
或直接執行:
Select a.inst_id,
c.sid,
c.serial#,
d.name,
b.object_name,
c.username,
a.object_id,
c.program,
c.status,
c.osuser,
c.terminal
from gv$Locked_object a, All_objects b, gv$session c, audit_actions d
where a.object_id = b.object_id
and a.inst_id = c.inst_id(+)
and a.session_id = c.sid(+)
and c.command = d.action;
2、Select * From v$session_longops 鎖表時間
Select * From dba_objects;
======================================
Select * from dba_locks where blocking_others like 'Blocking%';
---
Select a.inst_id,
c.sid,
c.serial#,
d.name,
b.object_name,
c.username,
a.object_id,
c.program,
c.status,
c.osuser,
c.terminal
from gv$Locked_object a, All_objects b, gv$session c, audit_actions d
where a.object_id = b.object_id
and a.inst_id = c.inst_id(+)
and a.session_id = c.sid(+)
and c.command = d.action;
可以檢視死鎖的狀態,死鎖的使用者,死鎖的機器以及死鎖的程式
select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)
Select * From v$locked_object;
Select * From dba_objects;
如果發生了鎖等待,我們可能更想知道是誰鎖了表而引起誰的等待
以下的語句可以查詢到誰鎖了表,而誰在等待。
以上查詢結果是一個樹狀結構,如果有子節點,則表示有等待發生。
如果想知道鎖用了哪個回滾段,還可以關聯到V$rollname,其中xidusn就是回滾段的USN
col user_name format a10
col owner format a10
col object_name format a10
col object_type format a10
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC
解鎖.c ____我只會解鎖.
(1)以系統管理員登陸
sqlplus name/passwd@dbname as sysdba
(2)檢視鎖
select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;
(3)把鎖給KILL掉
--alter system kill session 'sid,serial#';
alter system kill session '146,21177';
(4)給賬戶解鎖
sqlplus sys/oracle1234@remotedb as sysdba
alter user hbhp account unlock;
===============================
處理Oracle中殺不掉的鎖
一些ORACLE中的程式被殺掉後,狀態被置為"killed",但是鎖定的資源很長時間不釋放,有時實在沒辦法,只好重啟資料庫。現在提供一種方法解決這種問題,那就是在ORACLE中殺不掉的,在OS一級再殺。
1.下面的語句用來查詢哪些物件被鎖:
select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id = o.object_id and l.session_id=s.sid;
2.下面的語句用來殺死一個程式:
alter system kill session '24,111'; (其中24,111分別是上面查詢出的sid,serial#)
【注】以上兩步,可以透過Oracle的管理控制檯來執行。
3.如果利用上面的命令殺死一個程式後,程式狀態被置為"killed",但是鎖定的資源很長時間沒有被釋放,那麼可以在os一級再殺死相應的程式(執行緒),首先執行下面的語句獲得程式(執行緒)號:
select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=24 (24是上面的sid)
4.在OS上殺死這個程式(執行緒):
1)在unix上,用root身份執行命令:
#kill -9 12345(即第3步查詢出的spid)
2)在windows(unix也適用)用orakill殺死執行緒,orakill是oracle提供的一個可執行命令,語法為:
orakill sid thread
其中:
sid:表示要殺死的程式屬於的例項名
thread:是要殺掉的執行緒號,即第3步查詢出的spid。
例:c:>orakill orcl 12345
ORA-00031: session marked for kill
Cause: The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptible operation is done.
Action: No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner.
kill -9 12345
關鍵字: oracle鎖表問題 1、Select * From v$locked_object;鎖表物件
根據sessionid,到Tools--sessions找到相應的sessions即可看到鎖表的sql語句
或直接執行:
Select a.inst_id,
c.sid,
c.serial#,
d.name,
b.object_name,
c.username,
a.object_id,
c.program,
c.status,
c.osuser,
c.terminal
from gv$Locked_object a, All_objects b, gv$session c, audit_actions d
where a.object_id = b.object_id
and a.inst_id = c.inst_id(+)
and a.session_id = c.sid(+)
and c.command = d.action;
2、Select * From v$session_longops 鎖表時間
Select * From dba_objects;
======================================
Select * from dba_locks where blocking_others like 'Blocking%';
---
Select a.inst_id,
c.sid,
c.serial#,
d.name,
b.object_name,
c.username,
a.object_id,
c.program,
c.status,
c.osuser,
c.terminal
from gv$Locked_object a, All_objects b, gv$session c, audit_actions d
where a.object_id = b.object_id
and a.inst_id = c.inst_id(+)
and a.session_id = c.sid(+)
and c.command = d.action;
可以檢視死鎖的狀態,死鎖的使用者,死鎖的機器以及死鎖的程式
select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)
Select * From v$locked_object;
Select * From dba_objects;
如果發生了鎖等待,我們可能更想知道是誰鎖了表而引起誰的等待
以下的語句可以查詢到誰鎖了表,而誰在等待。
以上查詢結果是一個樹狀結構,如果有子節點,則表示有等待發生。
如果想知道鎖用了哪個回滾段,還可以關聯到V$rollname,其中xidusn就是回滾段的USN
col user_name format a10
col owner format a10
col object_name format a10
col object_type format a10
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC
解鎖.c ____我只會解鎖.
(1)以系統管理員登陸
sqlplus name/passwd@dbname as sysdba
(2)檢視鎖
select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;
(3)把鎖給KILL掉
--alter system kill session 'sid,serial#';
alter system kill session '146,21177';
(4)給賬戶解鎖
sqlplus sys/oracle1234@remotedb as sysdba
alter user hbhp account unlock;
===============================
處理Oracle中殺不掉的鎖
一些ORACLE中的程式被殺掉後,狀態被置為"killed",但是鎖定的資源很長時間不釋放,有時實在沒辦法,只好重啟資料庫。現在提供一種方法解決這種問題,那就是在ORACLE中殺不掉的,在OS一級再殺。
1.下面的語句用來查詢哪些物件被鎖:
select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id = o.object_id and l.session_id=s.sid;
2.下面的語句用來殺死一個程式:
alter system kill session '24,111'; (其中24,111分別是上面查詢出的sid,serial#)
【注】以上兩步,可以透過Oracle的管理控制檯來執行。
3.如果利用上面的命令殺死一個程式後,程式狀態被置為"killed",但是鎖定的資源很長時間沒有被釋放,那麼可以在os一級再殺死相應的程式(執行緒),首先執行下面的語句獲得程式(執行緒)號:
select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=24 (24是上面的sid)
4.在OS上殺死這個程式(執行緒):
1)在unix上,用root身份執行命令:
#kill -9 12345(即第3步查詢出的spid)
2)在windows(unix也適用)用orakill殺死執行緒,orakill是oracle提供的一個可執行命令,語法為:
orakill sid thread
其中:
sid:表示要殺死的程式屬於的例項名
thread:是要殺掉的執行緒號,即第3步查詢出的spid。
例:c:>orakill orcl 12345
ORA-00031: session marked for kill
Cause: The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptible operation is done.
Action: No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner.
kill -9 12345
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/90618/viewspace-671871/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 解決鎖表問題Oracle
- oracle 解鎖的問題Oracle
- oracle鎖表問題處理 v$lock v$locked_objectOracleObject
- oracle外來鍵索引解決父表鎖定問題Oracle索引
- Oracle死鎖一例(ORA-00060),鎖表導致的業務死鎖問題Oracle
- oracle 鎖問題的解決Oracle
- mysql innodb 索引失效問題引起表級鎖MySql索引
- 解決Oracle死鎖問題步驟Oracle
- oracle 檢視鎖表和解鎖Oracle
- Oracle的鎖表與解鎖Oracle
- Oracle觸發器死鎖問題解決Oracle觸發器
- 一個ORACLE死鎖問題的追蹤Oracle
- oracle 鎖表、解鎖的語句Oracle
- oracle表鎖住 解鎖辦法Oracle
- oracle檢視錶空間使用情況及某表是否被鎖的問題Oracle
- MySQL優化篇系列文章(二)——MyISAM表鎖與InnoDB鎖問題MySql優化
- 27、oracle的臨時表問題Oracle
- oracle檢視被鎖的表和解鎖Oracle
- 【故障-ORACLE】_NEXT_OBJE Exclusive鎖問題分析OracleOBJ
- oracle檢視鎖表程式Oracle
- ORACLE表和行的鎖Oracle
- Oracle資源正忙,要求指定NOWAIT——Oracle解鎖問題OracleAI
- synchronized鎖重入問題synchronized
- MySQL死鎖問題MySql
- 一次oracle行級鎖導致的問題Oracle
- Oracle 10g使用者鎖定的問題Oracle 10g
- [轉載]oracle手動鎖表Oracle
- 刪除oracle表被鎖住Oracle
- oracle殺死鎖表的程式Oracle
- MySQL鎖問題分析-全域性讀鎖MySql
- MySQL鎖等待與死鎖問題分析MySql
- 檢視oracle被鎖的表是誰鎖的Oracle
- 關於ORACLE的鎖表與解鎖總結Oracle
- 優化Oracle with全表掃描的問題優化Oracle
- 檢查Oracle的鎖狀態並清除問題會話Oracle會話
- 【故障-ORACLE】GGS_STICK出來大量鎖的問題Oracle
- MySQL 死鎖問題分析MySql
- Sqlserver分析死鎖問題SQLServer