Oracle死锁问题的解决
529907491
select sid,serial# from v$locked_object
SELECT bs.username "Blocking User", bs.username "DB User",
ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",
bs.serial# "Serial#", bs.sql_address "address",
bs.sql_hash_value "Sql hash", bs.program "Blocking App",
ws.program "Waiting App", bs.machine "Blocking Machine",
ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
ws.osuser "Waiting OS User", bs.serial# "Serial#",
ws.serial# "WSerial#",
DECODE (wk.TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'USER Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL USER LOCK',
'DX', 'Distributed Xaction',
'CF', 'Control FILE',
'IS', 'Instance State',
'FS', 'FILE SET',
'IR', 'Instance Recovery',
'ST', 'Disk SPACE Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'LOG START OR Switch',
'RW', 'ROW Wait',
'SQ', 'Sequence Number',
'TE', 'Extend TABLE',
'TT', 'Temp TABLE',
wk.TYPE
) lock_type,
DECODE (hk.lmode,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (hk.lmode)
) mode_held,
DECODE (wk.request,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (wk.request)
) mode_requested,
TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
DECODE
(hk.BLOCK,
0, 'NOT Blocking', /**//* Not blocking any other processes */
1, 'Blocking', /**//* This lock blocks other processes */
2, 'Global', /**//* This lock is global, so we can't tell */
TO_CHAR (hk.BLOCK)
) blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.SID = bs.SID(+)
AND wk.SID = ws.SID(+)
AND (bs.username IS NOT NULL)
AND (bs.username <> 'SYSTEM')
AND (bs.username <> 'SYS')
ORDER BY 1;
select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;
1）用dba用户执行以下语句
select username,lockwait,status,machine,program from v$session where sid in (select session_id from v$locked_object);
如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明：
Username：死锁语句所用的数据库用户；
Lockwait：死锁的状态，如果有内容表示被死锁。
Status： 状态，active表示被死锁
Machine： 死锁语句所在的机器。
Program： 产生死锁的语句主要来自哪个应用程序。
2）用dba用户执行以下语句，可以查看到被死锁的语句
select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in(select session_id from v$locked_object))
select sql_id from v$session where sid=499;
select sql_text from v$sql
select sql_text from v$sql where sql_id='2y73640n6bmqg'
四、死锁的解决方法
一般情况下，只要将产生死锁的语句提交就可以了，但是在实际的执行过程中。用户可
能不知道产生死锁的语句是哪一句。可以将程序关闭并重新启动就可以了。
经常在Oracle的使用过程中碰到这个问题，所以也总结了一点解决方法。
1）查找死锁的进程：
sqlplus "/as sysdba" (sys/change_on_install)
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;
2）kill掉这个死锁的进程：
alter system kill session '710,35184'; （其中sid=l.session_id）
3）如果还不能解决：
select pro.spid from v$session ses, v$process pro where ses.sid=319 and ses.paddr=pro.addr;
其中sid用死锁的sid替换:
exit
ps -ef|grep spid
其中spid是这个进程的进程号，kill掉这个Oracle进程。
ＤＢＡ解锁操作的流程！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
----------------------------------------- -------- ----------------------------
XIDUSN NUMBER
XIDSLOT NUMBER
XIDSQN NUMBER
OBJECT_ID NUMBER
SESSION_ID NUMBER
ORACLE_USERNAME VARCHAR2(30)
OS_USER_NAME VARCHAR2(30)
PROCESS VARCHAR2(12)
LOCKED_MODE NUMBER
SQL> select object_id,session_id,oracle_name,locked_mode from v$locked_object;
select object_id,session_id,oracle_name,locked_mode from v$locked_object;
*
ERROR at line 1:
ORA-00904: "ORACLE_NAME": invalid identifier
SQL> select OBJECT_ID,session_id,oracle_username,locked_mode from v$locked_object;
OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE
---------- ---------- ------------------------------ -----------
54007 245 T9OA 3
54007 251 T9OA 3
54106 284 T9OA 3
54063 284 T9OA 3
8990 340 3
8994 340 3
54007 420 T9OA 3
55777 456 DXSPIDER 3
54007 475 T9OA 3
54007 535 T9OA 3
54106 540 T9OA 3
OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE
---------- ---------- ------------------------------ -----------
54063 540 T9OA 3
9156 709 6
9229 709 3
54007 725 T9OA 3
54007 816 T9OA 3
54007 832 T9OA 3
54106 866 T9OA 3
54063 866 T9OA 3
54007 953 T9OA 3
54007 973 T9OA 3
54106 980 T9OA 3
OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE
---------- ---------- ------------------------------ -----------
54063 980 T9OA 3
54007 999 T9OA 3
24 rows selected.
SQL> desc v$lock
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(8)
KADDR RAW(8)
SID NUMBER
TYPE VARCHAR2(2)
ID1 NUMBER
ID2 NUMBER
LMODE NUMBER
REQUEST NUMBER
CTIME NUMBER
BLOCK NUMBER
SQL> select sid,type,lmode,request,block from v$lock where type='TX' or type='TM';
SID TY LMODE REQUEST BLOCK
---------- -- ---------- ---------- ----------
953 TX 0 6 0
832 TX 0 6 0
475 TX 0 6 0
535 TX 0 6 0
816 TX 0 6 0
420 TX 0 6 0
245 TX 0 6 0
725 TX 0 6 0
999 TX 0 6 0
340 TM 3 0 2
340 TM 3 0 2
SID TY LMODE REQUEST BLOCK
---------- -- ---------- ---------- ----------
284 TM 3 0 2
284 TM 3 0 2
980 TM 3 0 2
980 TM 3 0 2
709 TM 3 0 2
709 TM 6 0 2
973 TM 3 0 2
953 TM 3 0 2
832 TM 3 0 2
251 TM 3 0 2
475 TM 3 0 2
SID TY LMODE REQUEST BLOCK
---------- -- ---------- ---------- ----------
535 TM 3 0 2
816 TM 3 0 2
420 TM 3 0 2
245 TM 3 0 2
725 TM 3 0 2
999 TM 3 0 2
866 TM 3 0 2
866 TM 3 0 2
540 TM 3 0 2
540 TM 3 0 2
456 TM 3 0 2
SID TY LMODE REQUEST BLOCK
---------- -- ---------- ---------- ----------
340 TX 6 0 2
456 TX 6 0 2
980 TX 6 0 2
709 TX 6 0 2
866 TX 6 0 2
709 TX 6 0 2
284 TX 6 0 2
251 TX 6 0 1
540 TX 6 0 2
973 TX 6 0 1
43 rows selected.
SQL> select sql_id from v$session where sid=442;
SQL_ID
-------------
a0nvbna6ujt4b
SQL> select sql_text from v$sql where sql_id='a0nvbna6ujt4b';
SQL_TEXT
--------------------------------------------------------------------------------
UPDATE FLOW_FORM_TYPE SET PRINT_MODEL= :1 ,PRINT_MODEL_SHORT= :2 WHERE SEQ_ID
= :3
SQL> select sql_id from v$session where sid=973;
SQL_ID
-------------
a0nvbna6ujt4b
SQL> select sql_text from v$sql where sql_id='a0nvbna6ujt4b
2 ';
no rows selected
SQL> select sql_text from v$sql where sql_id='a0nvbna6ujt4b';
SQL_TEXT
--------------------------------------------------------------------------------
UPDATE FLOW_FORM_TYPE SET PRINT_MODEL= :1 ,PRINT_MODEL_SHORT= :2 WHERE SEQ_ID
= :3
SQL> desc v$session;
Name Null? Type
----------------------------------------- -------- ----------------------------
SADDR RAW(8)
SID NUMBER
SERIAL# NUMBER
AUDSID NUMBER
PADDR RAW(8)
USER# NUMBER
USERNAME VARCHAR2(30)
COMMAND NUMBER
OWNERID NUMBER
TADDR VARCHAR2(16)
LOCKWAIT VARCHAR2(16)
STATUS VARCHAR2(8)
SERVER VARCHAR2(9)
SCHEMA# NUMBER
SCHEMANAME VARCHAR2(30)
OSUSER VARCHAR2(30)
PROCESS VARCHAR2(12)
MACHINE VARCHAR2(64)
PORT NUMBER
TERMINAL VARCHAR2(30)
PROGRAM VARCHAR2(48)
TYPE VARCHAR2(10)
SQL_ADDRESS RAW(8)
SQL_HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
SQL_CHILD_NUMBER NUMBER
PREV_SQL_ADDR RAW(8)
PREV_HASH_VALUE NUMBER
PREV_SQL_ID VARCHAR2(13)
PREV_CHILD_NUMBER NUMBER
PLSQL_ENTRY_OBJECT_ID NUMBER
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER
PLSQL_OBJECT_ID NUMBER
PLSQL_SUBPROGRAM_ID NUMBER
MODULE VARCHAR2(48)
MODULE_HASH NUMBER
ACTION VARCHAR2(32)
ACTION_HASH NUMBER
CLIENT_INFO VARCHAR2(64)
FIXED_TABLE_SEQUENCE NUMBER
ROW_WAIT_OBJ# NUMBER
ROW_WAIT_FILE# NUMBER
ROW_WAIT_BLOCK# NUMBER
ROW_WAIT_ROW# NUMBER
LOGON_TIME DATE
LAST_CALL_ET NUMBER
PDML_ENABLED VARCHAR2(3)
FAILOVER_TYPE VARCHAR2(13)
FAILOVER_METHOD VARCHAR2(10)
FAILED_OVER VARCHAR2(3)
RESOURCE_CONSUMER_GROUP VARCHAR2(32)
PDML_STATUS VARCHAR2(8)
PDDL_STATUS VARCHAR2(8)
PQ_STATUS VARCHAR2(8)
CURRENT_QUEUE_DURATION NUMBER
CLIENT_IDENTIFIER VARCHAR2(64)
BLOCKING_SESSION_STATUS VARCHAR2(11)
BLOCKING_INSTANCE NUMBER
BLOCKING_SESSION NUMBER
SEQ# NUMBER
EVENT# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(8)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(8)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(8)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)
SERVICE_NAME VARCHAR2(64)
SQL_TRACE VARCHAR2(8)
SQL_TRACE_WAITS VARCHAR2(5)
SQL_TRACE_BINDS VARCHAR2(5)
ECID VARCHAR2(64)
SQL>
SQL> select SERIAL# from v$session where sid==251;
select SERIAL# from v$session where sid==251
*
ERROR at line 1:
ORA-00936: missing expression
SQL> select SERIAL# from v$session where sid=251;
SERIAL#
----------
17342
SQL>
SQL> select SERIAL# from v$session where sid=973;
SERIAL#
----------
10205
SQL> alter system kill session '251,17342';
alter system kill session '251,17342'
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL> SQL>
SQL> alter system kill session '973,10205';
alter system kill session '973,10205'
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL> select sid,type,request,lmode from v$lock;
SID TY REQUEST LMODE
---------- -- ---------- ----------
1058 XR 0 1
1052 XR 0 1
1058 CF 0 2
555 AF 0 4
1058 RS 0 2
340 WF 0 4
1059 RT 0 6
1067 DM 0 4
1059 RT 0 6
1067 RT 0 6
340 WF 0 6
SID TY REQUEST LMODE
---------- -- ---------- ----------
1067 MR 0 4
1067 MR 0 4
1067 MR 0 4
1067 MR 0 4
1067 MR 0 4
1067 MR 0 4
1057 TS 0 3
340 WF 0 6
1067 PW 0 3
340 WF 0 6
419 TO 0 3
SID TY REQUEST LMODE
---------- -- ---------- ----------
802 WF 6 0
709 SH 0 6
802 WP 0 6
419 JS 0 6
709 TT 0 4
709 HW 0 6
953 TX 6 0
832 TX 6 0
475 TX 6 0
535 TX 6 0
816 TX 6 0
SID TY REQUEST LMODE
---------- -- ---------- ----------
420 TX 6 0
245 TX 6 0
725 TX 6 0
999 TX 6 0
340 TM 0 3
340 TM 0 3
284 TM 0 3
284 TM 0 3
980 TM 0 3
980 TM 0 3
709 TM 0 3
SID TY REQUEST LMODE
---------- -- ---------- ----------
709 TM 0 6
973 TM 0 3
953 TM 0 3
832 TM 0 3
251 TM 0 3
475 TM 0 3
535 TM 0 3
816 TM 0 3
420 TM 0 3
245 TM 0 3
725 TM 0 3
SID TY REQUEST LMODE
---------- -- ---------- ----------
999 TM 0 3
866 TM 0 3
866 TM 0 3
540 TM 0 3
540 TM 0 3
456 TM 0 3
340 TX 0 6
456 TX 0 6
980 TX 0 6
709 TX 0 6
866 TX 0 6
SID TY REQUEST LMODE
---------- -- ---------- ----------
709 TX 0 6
284 TX 0 6
251 TX 0 6
540 TX 0 6
973 TX 0 6
71 rows selected.
SQL>
SQL> select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=251； 2 3
4
SQL> select spid, osuser, s.program
2 from v$session s,v$process p
3 where s.paddr=p.addr and s.sid=251;
SPID OSUSER
------------ ------------------------------
PROGRAM
------------------------------------------------
7141 root
JDBC Thin Client
SQL> !
[oracle@dxzs1 ~]$ ps -ef | grep 7141
oracle 7141 1 99 Oct26 ? 4-09:41:16 oracledxoa1 (LOCAL=NO)
oracle 18540 18453 0 21:26 pts/1 00:00:00 grep 7141
[oracle@dxzs1 ~]$ kill -9 7141
[oracle@dxzs1 ~]$ ps -ef | grep 7141
oracle 18772 18453 0 21:27 pts/1 00:00:00 grep 7141
[oracle@dxzs1 ~]$ exit
exit
SQL> select spid, osuser, s.program
2 from v$session s,v$process p
3 where s.paddr=p.addr and s.sid=973;
SPID OSUSER
------------ ------------------------------
PROGRAM
------------------------------------------------
10720 root
JDBC Thin Client
SQL> !
[oracle@dxzs1 ~]$ ps -ef |grep 10720
oracle 10720 1 97 Oct26 ? 4-10:02:44 oracledxoa1 (LOCAL=NO)
oracle 19716 19641 0 21:29 pts/1 00:00:00 grep 10720
[oracle@dxzs1 ~]$ kill -9 10720
[oracle@dxzs1 ~]$ ps -ef |grep 10720
oracle 19855 19641 0 21:29 pts/1 00:00:00 grep 10720
[oracle@dxzs1 ~]$ exit
exit
SQL> select sid,type,request,block from v$lock;
SID TY REQUEST BLOCK
---------- -- ---------- ----------
1058 XR 0 2
1052 XR 0 2
1058 CF 0 2
555 AF 0 2
1058 RS 0 2
340 WF 0 2
1059 RT 0 2
1067 DM 0 2
1059 RT 0 2
1067 RT 0 2
340 WF 0 2
SID TY REQUEST BLOCK
---------- -- ---------- ----------
1067 MR 0 2
1067 MR 0 2
1067 MR 0 2
1067 MR 0 2
1067 MR 0 2
1067 MR 0 2
1057 TS 0 2
340 WF 0 2
1067 PW 0 2
340 WF 0 1
419 TO 0 2
SID TY REQUEST BLOCK
---------- -- ---------- ----------
802 WF 6 0
709 SH 0 0
802 WP 0 2
419 JS 0 2
709 TT 0 2
709 HW 0 2
340 TM 0 2
340 TM 0 2
284 TM 0 2
284 TM 0 2
980 TM 0 2
SID TY REQUEST BLOCK
---------- -- ---------- ----------
980 TM 0 2
709 TM 0 2
709 TM 0 2
866 TM 0 2
866 TM 0 2
540 TM 0 2
540 TM 0 2
456 TM 0 2
340 TX 0 2
456 TX 0 2
980 TX 0 2
SID TY REQUEST BLOCK
---------- -- ---------- ----------
709 TX 0 2
866 TX 0 2
709 TX 0 2
284 TX 0 2
540 TX 0 2
49 rows selected.
SQL> ^A]^H^H^H^H
SP2-0042: unknown comman" - rest of line ignored.
SQL>
**************************************************************************************************************************
-- 死锁查询语句
SELECT bs.username "Blocking User", bs.username "DB User",
ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",
bs.serial# "Serial#", bs.sql_address "address",
bs.sql_hash_value "Sql hash", bs.program "Blocking App",
ws.program "Waiting App", bs.machine "Blocking Machine",
ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
ws.osuser "Waiting OS User", bs.serial# "Serial#",
ws.serial# "WSerial#",
DECODE (wk.TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'USER Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL USER LOCK',
'DX', 'Distributed Xaction',
'CF', 'Control FILE',
'IS', 'Instance State',
'FS', 'FILE SET',
'IR', 'Instance Recovery',
'ST', 'Disk SPACE Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'LOG START OR Switch',
'RW', 'ROW Wait',
'SQ', 'Sequence Number',
'TE', 'Extend TABLE',
'TT', 'Temp TABLE',
wk.TYPE
) lock_type,
DECODE (hk.lmode,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (hk.lmode)
) mode_held,
DECODE (wk.request,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (wk.request)
) mode_requested,
TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
DECODE
(hk.BLOCK,
0, 'NOT Blocking', /**//* Not blocking any other processes */
1, 'Blocking', /**//* This lock blocks other processes */
2, 'Global', /**//* This lock is global, so we can't tell */
TO_CHAR (hk.BLOCK)
) blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.SID = bs.SID(+)
AND wk.SID = ws.SID(+)
AND (bs.username IS NOT NULL)
AND (bs.username <> 'SYSTEM')
AND (bs.username <> 'SYS')
ORDER BY 1;
查询发生死锁的select语句
select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object));
关于数据库死锁的检查方法
一、数据库死锁的现象
程序在执行的过程中，点击确定或保存按钮，程序没有响应，也没有出现报错。
二、死锁的原理
当对于数据库某个表的某一列做更新或删除等操作，执行完毕后该条语句不提
交，另一条对于这一列数据做更新操作的语句在执行的时候就会处于等待状态，
此时的现象是这条语句一直在执行，但一直没有执行成功，也没有报错。
三、死锁的定位方法
通过检查数据库表，能够检查出是哪一条语句被死锁，产生死锁的机器是哪一台。
1）用dba用户执行以下语句
select username,lockwait,status,machine,program from v$session where sid
in (select session_id from v$locked_object)
如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明：
Username：死锁语句所用的数据库用户；
Lockwait：死锁的状态，如果有内容表示被死锁。
Status： 状态，active表示被死锁
Machine： 死锁语句所在的机器。
Program： 产生死锁的语句主要来自哪个应用程序。
2）用dba用户执行以下语句，可以查看到被死锁的语句
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))
四、死锁的解决方法
一般情况下，只要将产生死锁的语句提交就可以了，但是在实际的执行过程中。用户可
能不知道产生死锁的语句是哪一句。可以将程序关闭并重新启动就可以了。
经常在Oracle的使用过程中碰到这个问题，所以也总结了一点解决方法。
1）查找死锁的进程：
sqlplus "/as sysdba" (sys/change_on_install)
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S
WHERE l.SESSION_ID=S.SID;
2）kill掉这个死锁的进程：
alter system kill session ‘sid,serial#’; （其中sid=l.session_id）
3）如果还不能解决：
select pro.spid from v$session ses, v$process pro where ses.sid=729 and ses.paddr=pro.addr;
其中sid用死锁的sid替换:
exit
ps -ef|grep spid
在进行数据库管理的过程中,经常会出现数据表被用户的一些不合理操作而导致表被锁定的情况,以下主要介绍如何查找哪些表被哪个用户所锁定,以及如何解除锁定:
1.查找被锁定的表:
select object_name,session_id,os_user_name,oracle_username,process,locked_mode,status
from v$locked_object l, all_objects a
where l.object_id=a.object_id;
如果想知道具体是哪个进程阻塞了哪个进程,可用以下语句查看:
select username,v$lock.sid,trunc(id1/power(2,16)) rbs,bitand(id1,to_number('ffff','xxxx'))+0 slot,id2 seq,lmode,request from v$lock, v$session where v$lock.type = 'TX' and v$lock.sid = v$session.sid and v$session.username = 'CENTER' ;
或
select
(select username||':'||sid||':'||serial# from v$session where sid=a.sid) ||
' 阻塞了 ' ||
(select username ||':'||sid||':'||serial# from v$session where sid=b.sid)
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
2.确定锁定表用户的sid与serial编号(可通过oracle用户确定也可通过系统用户确定)
a.通过oracle用户确定
select sid,serial# from v$session where username='TAYA';
b.通过系统用户确定
select sid,serial# from v$session where username=’administrator’;
3.杀掉造成死锁的进程(已知是123阻塞了124号进程,杀掉即可解锁)
alter system kill session ‘123,3935’;
722 1772
729 586
至此表死锁解除,现在存在的是数据表正常的锁定,等事务提交后自然消失.
set
SELECT event, sum(decode(wait_time,0,1,0)) "Curr",
sum(decode(wait_time,0,0,1)) "Prev",
count(*) "Total"
FROM v$session_wait GROUPBY event ORDERBYcount(*);
alter system set sga_max_size=1553m scope=spfile sid='dxoa2'
alter system set sga_target=1553m scope=spfile sid='dxoa2'
alter system set sga_max_size=8000m scope=spfile sid='dxoa1'
alter system set sga_target=8000m scope=spfile sid='dxoa1'
srvctl stop instance -d dxoa -i dxoa1
srvctl start instance -d dxoa -i dxoa1
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
