解决oracle死锁.doc

一个解决过程，仅供参考，呵呵
SQL> conn system/manager
Connected.
SQL>
SQL>
SQL> select * from v$locked_object;

XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID
---------- ---------- ---------- ---------- ----------
ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE
------------------------------ --------------- --------- -----------
5 47 240 5386 18
YANJIBS account 18158 3

5 47 240 5467 18
YANJIBS account 18158 3

SQL>
SQL> select sid, serial#, username, program from v$session where sid = '18';

SID SERIAL# USERNAME
---------- ---------- ------------------------------
PROGRAM
------------------------------------------------
18 6548 YANJIBS
sqlplus@jl_yj (TNS V1-V3)

SQL>
SQL> alter system kill session '18, 6548';

System altered.

SQL>
SQL>
SQL> select sid, serial#, username, program from v$session where sid = '18';

SID SERIAL# USERNAME
---------- ---------- ------------------------------
PROGRAM
------------------------------------------------
18 6548 YANJIBS
sqlplus@jl_yj (TNS V1-V3)

SQL>
SQL> alter system kill session '18, 6548';

System altered.

SQL> alter system kill session '18, 6548';

System altered.

SQL> select sid, serial#, username, program from v$session where sid = '18';

SID SERIAL# USERNAME
---------- ---------- ------------------------------
PROGRAM
------------------------------------------------
18 6548 YANJIBS
sqlplus@jl_yj (TNS V1-V3)

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
COMMAND NUMBER
OWNERID NUMBER
TADDR VARCHAR2(16)
LOCKWAIT VARCHAR2(16)
STATUS VARCHAR2(8)
SERVER VARCHAR2(9)
SCHEMA# NUMBER
SCHEMANAME VARCHAR2(30)
OSUSER VARCHAR2(15)
PROCESS VARCHAR2(9)
MACHINE VARCHAR2(64)
TERMINAL VARCHAR2(10)
PROGRAM VARCHAR2(48)
TYPE VARCHAR2(10)
SQL_ADDRESS RAW(8)
SQL_HASH_VALUE NUMBER
PREV_SQL_ADDR RAW(8)
PREV_HASH_VALUE 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)

SQL>
SQL> !sh
$ ps -ef|grep sqlplus
account 14710 28194 0 17:31:55 pts/3 0:00 grep sqlplus
account 18158 17664 0 16:07:54 pts/2 0:00 sqlplus
oracle 18212 24732 0 16:59:36 pts/4 0:00 sqlplus system/manager
account 23326 24956 0 17:05:43 pts/3 0:00 sqlplus
$
$ exit

SQL> select process from v$session where sid = '18';

PROCESS
---------
18158

SQL> !sh
$ ps -ef|grep 18158
account 14712 28196 0 17:33:04 pts/3 0:00 grep 18158
account 18158 17664 0 16:07:54 pts/2 0:00 sqlplus
account 19490 18158 0 16:08:05 - 0:00 oraclectc (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
$ kill 18158
$ ps -ef|grep 18158
account 27748 28196 0 17:33:00 pts/3 0:00 grep 18158
$
$
$ exit

SQL> select * from v$locked_object;

no rows selected