对一个DROP MV 语句长时间无返回的处理步骤。

接到客户电话说一个drop mview语句 花了4个小时没有结果。
客户说建立一个MV但觉得不对想删除但删除语句一直无返回。

我按如下步骤对库进行检查。


SQL> set head off
SQL> select sid,OPNAME,sofar,TOTALWORK,trunc(sofar/totalwork*100,2)||'%' as perwork,elapsed_seconds,message
  2   from v$session_longops where sofar!=totalwork;


        57
Rowid Range Scan
    453033     934531
48.47%
            446
Rowid Range Scan:  SGREPORTS.SUMMARY: 453033 out of 934531 Blocks done

        26
Table Scan
      8829      57660
15.31%
             36
Table Scan:  CNSADMIN.CNSRPT_AUDIT: 8829 out of 57660 Blocks done


        15
SYS_EXPORT_FULL_41
     21238      47705
44.51%
          18308
SYS_EXPORT_FULL_41: EXPORT : 21238 out of 47705 MB done

        46
Rowid Range Scan
      9288      66726
13.91%
             91

Rowid Range Scan:  REPORTER.REPORTER_STATUS_MV: 9288 out of 66726 Blocks done

       109
Rowid Range Scan
      8068      94717
8.51%
             16
Rowid Range Scan:  REPORTER.REPORTER_STATUS_MV: 8068 out of 94717 Blocks done

        36
Sort Output
     72807      74632
97.55%

           1488
Sort Output:  : 72807 out of 74632 Blocks done


已选择6行。

没发现DROP　MV语句。

SQL> select * from dba_blockers;

            221


SQL>  select sql_address from v$session where sid =221

SQL_ADDRESS
----------------
00

表示没有事务在运行。但有可能保有锁。

SQL>
SQL>  SELECT
  2       a.session_id, username,type,mode_held,mode_requested,
  3       lock_id1,lock_id2
  4  FROM
  5       sys.v_$session b,
  6       sys.dba_blockers c,
  7       sys.dba_lock a
  8  WHERE
  9       c.holding_session=a.session_id AND
 10       c.holding_session=b.sid
 11  /

       221 REPORTER
USER
Exclusive
None
0
1264

       221 REPORTER
USER
Exclusive
None
44178
0


       221 REPORTER
USER
Row-X (SX)
None
212
0

       221 REPORTER
USER
Exclusive
None
65537

173202


果然在221语句上有锁。

分析：一定是客户在建立MV时设定了刷新，所以对应的JOB启动。但没等待JOB刷新结束，客户又启动了DROP　MV.
SQL> set head on
SQL>
select * from dba_jobs_running;

       SID        JOB   FAILURES LAST_DATE
---------- ---------- ---------- --------------
LAST_SEC                                         THIS_DATE
------------------------------------------------ --------------
THIS_SEC                                           INSTANCE
------------------------------------------------ ----------
        36        305          0 04-10月-08
07:24:46                                         05-10月-08
07:24:50                                                  0

       221       1264　　　　　－－－－－这个就是那个造成DROP　MV　BLOCK的JOB。



       SID        JOB   FAILURES LAST_DATE
---------- ---------- ---------- --------------
LAST_SEC                                         THIS_DATE
------------------------------------------------ --------------
THIS_SEC                                           INSTANCE
------------------------------------------------ ----------


SQL> select * from dba_jobs where job=1264;

未选定行



SQL>  select s.sid,s.serial#,s.username,s.status,p.spid
  2        from v$session s, v$process p
  3        where s.sid in (221) and s.paddr = p.addr;

       SID    SERIAL#
---------- ----------
USERNAME                                                     STATUS
------------------------------------------------------------ ----------------
SPID
------------------------
       221      27367
REPORTER                                                     ACTIVE
23087

果然有一个JOB　HANG到这了。
alter system kill session '221,27367';
from OS:
kill -9 23087
一切OK了。