业务需要,需要重新创建物化视图,这里顺便提一句,如果知道怎么重命名物化视图,麻烦教我一下
drop materialized view appclient.user_role;
执行删除语句,结果就一直在执行,换了个窗口查了下对应的sid,然后查了下v$session_wait
drop materialized view appclient.user_role;
执行删除语句,结果就一直在执行,换了个窗口查了下对应的sid,然后查了下v$session_wait
SQL> select sid,seq#,event from v$session_wait where sid=238;
SID SEQ# EVENT
---------- ---------- ----------------------------------------------------------------
238 405 enq: JI - contention
这个等待事件头次见,上网查了一下,有人查过metalink,对我这种在穷公司的人来说是好事
Symptoms
Refresh session is waiting for enq: JI - contention.
10046 trace of the waiting refresh session shows repeating messages like the following:
WAIT #4: nam='enq: JI - contention' ela= 489125 name|mode=1246298118 view object #=78868 0=0 obj#=-1 tim=1285069467387125
Cause
JI enqueue is used to serialize the refresh of an materialized view object, JI enqueue is acquired when a materialized view refresh is being performed on an aggregate join view (AJV), is used to ensure that two or more refresh processes do not try to refresh the same object.
Solution
You may want to see which session is holding the JI enqueue with the help of Note 1020008.6 or Note 1020007.6.
If the JI enqueue holder is an intended refresh session, the other session(s) that intend to refresh the same mview may just need to wait until the first one finishes.
If however the JI enqueue holder session is stuck (e.g. was killed without the immediate option), then you may want to take further actions to remove/terminate this session, so that the intended refresh can acquire the JI enqueue on the mview and proceed with the refresh.
大体意思就是有其他session抢占资源,所以一直在等待。突然想到刷新物化视图的job,于是查了下正在执行的job
Symptoms
Refresh session is waiting for enq: JI - contention.
10046 trace of the waiting refresh session shows repeating messages like the following:
WAIT #4: nam='enq: JI - contention' ela= 489125 name|mode=1246298118 view object #=78868 0=0 obj#=-1 tim=1285069467387125
Cause
JI enqueue is used to serialize the refresh of an materialized view object, JI enqueue is acquired when a materialized view refresh is being performed on an aggregate join view (AJV), is used to ensure that two or more refresh processes do not try to refresh the same object.
Solution
You may want to see which session is holding the JI enqueue with the help of Note 1020008.6 or Note 1020007.6.
If the JI enqueue holder is an intended refresh session, the other session(s) that intend to refresh the same mview may just need to wait until the first one finishes.
If however the JI enqueue holder session is stuck (e.g. was killed without the immediate option), then you may want to take further actions to remove/terminate this session, so that the intended refresh can acquire the JI enqueue on the mview and proceed with the refresh.
大体意思就是有其他session抢占资源,所以一直在等待。突然想到刷新物化视图的job,于是查了下正在执行的job
SQL> select * from dba_jobs_running;
SID JOB FAILURES LAST_DATE LAST_SEC THIS_DATE THIS_SEC INSTANCE
---------- ---------- ---------- ------------------- ---------------- ------------------- ---------------- ----------
253 61 0 2012-01-31 04:31:09 04:31:09 2012-01-31 04:41:10 04:41:10 0
果然是卡死了,于是手动清除,一直在用从eagle那学来的方法
果然是卡死了,于是手动清除,一直在用从eagle那学来的方法
SQL> select sid,serial# from v$session where sid=253;
SID SERIAL#
---------- ----------
253 14396
SQL> alter system kill session '253,14396';
alter system kill session '253,14396'
*
第 1 行出现错误:
ORA-00031: 标记要终止的会话
SQL> select spid from v$process where addr IN (SELECT x.ADDR FROM x$ksupr x,v$session s WHERE s.paddr(+)=x.addr and bit
);
SPID
------------
2824
登录到服务器上使用
orakill 2824
等一会,卡住的job就被删掉了,删除物化视图成功
登录到服务器上使用
orakill 2824
等一会,卡住的job就被删掉了,删除物化视图成功
SQL> drop materialized view appclient.user_role;
实体化视图已删除。
SQL> commit;
提交完成。
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25680865/viewspace-715428/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25680865/viewspace-715428/