问题描述:
接到应用人员的报告,说是在任何对表CSNOZ629926699966的操作都会hang,包括desc CSNOZ629926699966,例如:
ora9i@cs_dc02:/ora9i > sqlplus pubuser/pubuser
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jan 10 10:11:06 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Clusters options
JServer Release 9.2.0.4.0 - Production
SQL> conn pubuser/pubuser
Connected.
SQL> desc CSNOZ629926699966
。。。
这个进程 hang 了
。。。
询问了一下之前有无特别的操作,业务人员说很久以前执行了脚本,但是该教本运行很久都没有结果,然后他就退出了会话,再之后,就出现了上面的情况。脚本内容如下:$ cat CSNOZ629926699966.sh
#!/bin/sh
sqlplus pubuser/pubuser@csmisc << EOF #use your username/password
create table CSNOZ629926699966 as select * from CSNOZ62992266cs
where mid not in ( select mid from pubuser.SUBSCRIPTION_BAK_200412@newdb where servid='020999011964' and status in ('A','B','S'));
exit;
$
$
$
$
解决过程:ora9i@cs_dc02:/ora9i > sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jan 10 10:19:13 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Clusters options
JServer Release 9.2.0.4.0 - Production
SQL> select * from v$lock where block=1;
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL>
我们看到目前没有锁的信息
SQL> select xidusn, object_id, session_id, locked_mode from v$locked_object;
。。。
XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- -----------
14 18 37 3
。。。
SQL> /
。。。
XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- -----------
14 18 37 3
。。。
SQL> /
。。。
XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- -----------
14 18 37 3
。。。
SQL>
查找 v$locked_object,我们发现了一个可疑的会话,SID 37:
SQL> select object_name,owner,object_type from dba_objects where object_id=18;
。。。 。。。
OBJECT_NAME OWNER OBJECT_TYPE
------------------------------ ------------------------------ ------------------
OBJ$ SYS TABLE
。。。 。。。
SQL>
奇怪怎么一直有这个锁??
初步猜测是由于SID为37的会话执行了上面的DDL语句,并在语句未完成前异常退出,
造成了所有访问那个(DDL语句中涉及到的)对象的进程都hang了。
接下来我们看看等待事件:
SQL> select event,sid,p1,p2,p3 from v$session_wait where event not like 'SQL*%' and event not like 'rdbms%';
EVENT P1 P2 SID
---------------------------------------------------------------- ---------- ---------- ----------
pmon timer 300 0 1
ges remote message 32 0 4
gcs remote message 64 0 5
gcs remote message 64 0 7
smon timer 300 0 19
library cache lock 1.3835E+19 1.3835E+19 30wakeup time manager 0 0 22
7 rows selected.
SQL> /
EVENT P1 P2 SID
---------------------------------------------------------------- ---------- ---------- ----------
pmon timer 300 0 1
ges remote message 32 0 4
gcs remote message 64 0 5
gcs remote message 64 0 7
smon timer 300 0 19
library cache lock 1.3835E+19 1.3835E+19 30wakeup time manager 0 0 22
7 rows selected.
SQL> /
EVENT P1 P2 SID
---------------------------------------------------------------- ---------- ---------- ----------
pmon timer 300 0 1
ges remote message 32 0 4
gcs remote message 64 0 5
gcs remote message 64 0 7
smon timer 300 0 19
library cache lock 1.3835E+19 1.3835E+19 30wakeup time manager 0 0 22
7 rows selected.
SQL> /
EVENT P1 P2 SID
---------------------------------------------------------------- ---------- ---------- ----------
pmon timer 300 0 1
ges remote message 32 0 4
gcs remote message 64 0 5
gcs remote message 64 0 7
smon timer 300 0 19
library cache lock 1.3835E+19 1.3835E+19 30wakeup time manager 0 0 22
7 rows selected.
SQL>
我们注意到下面的事件:
EVENT P1 P2 SID
---------------------------------------------------------------- ---------- ---------- ----------
。。。
library cache lock 1.3835E+19 1.3835E+19 30
。。。
P1 是句柄地址(handle address),也就是'library cache lock'发生的地址。
P2 是一个状态对象,在这里,它表示在对象上加载的锁的地址(lock address)。
P1 和 P2都是科学计数发表示的10进制数。
这些信息再次证实了上面的猜测,SID 37阻塞了SID 30。
找出这两个可疑进程的sid和serial,然后对他们设置10046事件:SQL> select sid,serial# from v$session where sid in (30,37);
SID SERIAL#
---------- ----------
30 24167
37 2707
SQL> exec dbms_system.set_ev(30,24167,10046,12,'');
PL/SQL procedure successfully completed.
SQL> exec dbms_system.set_ev(37,2707,10046,12,'');
PL/SQL procedure successfully completed.
SQL>
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18921899/viewspace-1017537/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18921899/viewspace-1017537/