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

?

Oracle死鎖問題的解決