问题描述：
接到应用人员的报告，说是在任何对表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         30
wakeup time manager                                                       0          0         22

7 rows selected.

SQL> /

EVENT                                                                    P1         P2        SID
---------------------------------------------------------------- ---------- ---------- ----------
pmon timer                                                              300          0          1
ges remote message                                                       32          0        &

[1] [2] [3] [4] [5] [6] [7] 下一页