I noticed recently that calling dbms_mview.refresh(...), which refreshes materialized views in Oracle, has an implicit commit.
Any ideas - other than "because it does" - why this action has an implicit commit?
I noticed recently that calling dbms_mview.refresh(...), which refreshes materialized views in Oracle, has an implicit commit.
Any ideas - other than "because it does" - why this action has an implicit commit?
According to Tom Kyte it is because a decision was made at design time to consider refreshing to be a DDL operation. Since all DDL operations implicitly commit, so does this one. Unfortunatly he doesn't answer the resulting question of why they choose to make it DDL.
Depending no your Oracle version and/or the parameters you supply dbms_mview.refresh may be doing a TRUNCATE followed by a direct load. TRUNCATE is a DDL command and as such issues an implicit commit. Direct load does not require a commit.
If you are using a more recent version of Oracle, I think 10.2+, you can set the atomic_refresh parameter to TRUE and it will refresh within a single transaction, using standard DELETE / INSERTs. This method could be quite a bit slower though.
A work arround is to do the call to dbms_mview.refresh in an autonomous transaction (create a PL/SQL procedure with pragma autonomous_transaction).