接到客户电话说一个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了。