Complete refresh of a single materialized view used to do a truncate and insert on the mview table until 10G. Starting with 10G the refresh does a delete and insert on the mview table. This guarantees that the table is never empty in case of an error, the refresh process became an atomic operation.
There is another difference between 9.2 and 10G in the refresh process, which I have realized when trying to find out why a DDL trigger prevented a refresh operation. In 9.2 the refresh process runs an ALTER SUMMARY statement against the mview while in 10G it does not.
We have a DDL trigger in some of the test environments (all 9.2) for preventing some operations. The trigger checks the type of the DDL and allows or disallows it based on some conditions. After this trigger was put in place some developers started to complain about some periodic mview refresh operations failing a few weeks ago. I knew it was not because of the TRUNCATE because the trigger allowed truncate operations.
So I enabled sql tracing and found out what the refresh process was doing. Here is a simplified test case for it in 10G.
YAS@10G>create table master as select * from all_objects where rownum<=5;
Table created.
YAS@10G>alter table master add primary key (owner,object_name,object_type);
Table altered.
YAS@10G>create materialized view mview as select * from master;
Materialized view created.
YAS@10G>exec dbms_mview.refresh('MVIEW');
PL/SQL procedure successfully completed.
YAS@10G>create or replace trigger ddl_trigger before ddl on schema
2 begin
3 if ora_sysevent<>'TRUNCATE' then
4 raise_application_error(-20001,'DDL NOT ALLOWED');
5 end if;
6 end;
7 /
Trigger created.
YAS@10G>exec dbms_mview.refresh('MVIEW');
PL/SQL procedure successfully completed.
The refresh was successful in 10G even after the DDL trigger was active. In 9.2 it errors out after the DDL trigger is enabled.
SQL> exec dbms_mview.refresh('MVIEW');
BEGIN dbms_mview.refresh('MVIEW'); END;
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of YAS.MVIEW
ORA-20001: DDL NOT ALLOWED
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858
ORA-06512: at line 1
After enabling sql trace and running the refresh again, the trace file shows the problem statement.
SQL> alter session set sql_trace=true;
Session altered.
SQL> exec dbms_mview.refresh('MVIEW');
BEGIN dbms_mview.refresh('MVIEW'); END;
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of YAS.MVIEW
ORA-20001: DDL NOT ALLOWED
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858
ORA-06512: at line 1
The last statement in the trace file is this:
ALTER SUMMARY "YAS"."MVIEW" COMPILE
So, in 9.2 refresh process runs an ALTER SUMMARY command against the mview. After changing the trigger to allow DDL operations on SUMMARY objects I could refresh the mview without errors.
Tonguc Yilmaz had a recent post about using 10046 for purposes other than performance tuning, like finding out what statements a procedure runs. I find uses for sql tracing nearly everyday and the above case is one of them. When you are not able to understand why an error happens, it is sometimes very useful to turn on sql tracing and examine the trace file. Errors related to specific bind values, errors raised from a "when others" block (you know we do not like "when others", right?) are a couple of things you can dig and analyze with sql tracing.
SQL> r
1 create or replace trigger ddl_trigger before ddl on schema
2 begin
3 if ora_sysevent<>'TRUNCATE' and ora_dict_obj_type<>'SUMMARY' then
4 raise_application_error(-20001,'DDL NOT ALLOWED');
5 end if;
6* end;
Trigger created.
SQL> exec dbms_mview.refresh('MVIEW');
PL/SQL procedure successfully completed.
