Mview refresh when involved table is locked
- From: Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 21 Nov 2013 20:16:49 -0200
Hi,
I am facing this problem.
When the refresh COMPLETE ON DEMAND of a mview starts via job
(automatically from user_jobs), as schedulled and some tables (at least
10) used in the mview are locked , the refresh also keep in a recursive
lock and anything more run ...
I need to kill sessions to release process...
Among these objects used in the mview, I have tables and also others mviews
(with refresh ON COMMIT) , so , some MLOG$_ are locked.
The refresh of mview try to execute this command and stays stopped.
/* QSMQ VALIDATION */ ALTER SUMMARY "SCHEMA001"."MATVIEW001" COMPILE
I did a test as below and this situation did not get lock doing the refresh:
create table eri_tab
( coluna1 varchar2(30)
, coluna2 varchar2(30)
);
DROP MATERIALIZED VIEW mv_eri1;
CREATE MATERIALIZED VIEW mv_eri1
( coluna1
, coluna2
)
REFRESH COMPLETE ON DEMAND
NEXT sysdate + 1/12
AS
SELECT coluna1, coluna2
FROM eri_tab
/
insert into eri_tab values ('valor1a', 'valor1b');
insert into eri_tab values ('valor2a', 'valor2b');
insert into eri_tab values ('valor3a', 'valor3b');
at this point the table eri_tab is locked, i didnot do the COMMIT.
so, in another session I got sucess doing the mview refresh:
DECLARE
BEGIN
dbms_refresh.refresh('"SCHEMA001"."MV_ERI1"');
END;
/
I saw in the net the parameter : QUERY_REWRITE_INTEGRITY
and tried the stale_tolerated , via alter session, before start the refresh
in the first scenary, with no success.
My doubt is:
Why the refresh of mview stopped running this command in the first scenary
?
/* QSMQ VALIDATION */ ALTER SUMMARY "SCHEMA001"."MATVIEW001" COMPILE
and it did not occured in my sample ?
Does anyone has any experience with this situation ?
Regards
Eriovaldo
Other related posts:
