ORA-12034: materialized view log younger than last refresh

Dabblers in the art of the materialisation of views may be used to seeing this error message, and it's often a portent of doom. Metalink Note 204127.1 tackles it head on with a list of possible causes, but is remiss in advising that a complete refresh is required.

And thank goodness for that. A multi-gigabyte materialized view based on a multi-multi-gigabyte master table is no joke to perform a complete refresh on, and anything we can do to avoid it is welcome.

As far as data warehousing is concerned we can trim the fat out of the Metalink note to shortlist our potential causes:

• Previous refresh failed
• Modifying the master table so that changes don't go to the materialized view log (truncate, alter partitions ... drop / truncate ... etc)
• Master table reorganization

And here is another prime cause that the note doesn't identify:

• Issuing an MV log purge.

All of these potential causes appear to be easily fixable through the simple mechanism of dropping and recreating the materialized view. But I'm not talking about dropping the data here. One of the requirements for this method to be successful is that the materialized view was created on a prebuilt table. If this is the case, and it is possible to find out if it is or not based on the BUILD_MODE column of USER_MVIEWS, then in the event of a refresh failure we can:

1. Drop the materialized view.
2. Do whatever is required (if anything) to bring the master table and the MV back into synchronization (delete data from master, or add data to MV).
3. Recreate the MV, again using the ON PREBUILT TABLE clause obviously.
4. Erm ...
5. That's it.

Doubtless you script hounds will be wanting to see a test of this, huh?

create table m (col1 number);

create materialized view log on m
with rowid (col1)
including new values;

create table
     m_mv_cstar
as
select count(*) c_star
from   m;

create materialized view m_mv_cstar
 on prebuilt table
 refresh fast
 on commit
as
select count(*) c_star
from   m;

insert into m values (1);

commit;

That gets us a successfully fast refreshing materialized view. Now we break it.

drop materialized view log on m;

create materialized view log on m
with rowid (col1)
including new values;

insert into m values (1);

commit;

That ought to get you the ORA-12034 error, and this is the time to go and check whether any other materialized views has successfully refreshed and whether the change to the master table is still intact or whether that has been rolled back. In this case the change to m ought to have been rolled back.

So now we fix the problem of the ORA-12034 without a complete refresh of the materialized view.

drop  materialized view m_mv;

create materialized view m_mv
  on prebuilt table
  refresh fast
  on commit
as
select count(*) c_star
from   m;

If by some chance you had mislaid the definition of your materialized view then use the following to retrieve it.

Set Long 100000 Pagesize 0

Select  DBMS_Metadata.Get_DDL('MATERIALIZED_VIEW','M_MV_CSTAR') From Dual;

Note the underscore in the MATERIALIZED_VIEW there. Forget that and you'll receive a baffling:

 1* Select  DBMS_Metadata.Get_DDL('MATERIALIZED VIEW','M_MV_CSTAR') From Dual
SQL> /
ERROR:
ORA-31600: invalid input value MATERIALIZED VIEW for parameter OBJECT_TYPE in
function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 2576
ORA-06512: at "SYS.DBMS_METADATA", line 2627
ORA-06512: at "SYS.DBMS_METADATA", line 4220
ORA-06512: at line 1

So have we successfully recovered from the error?

insert into m values (1);

commit;

select * from m;

select * from m_mv_cstar;

Hopefully that demonstrates that we have.

So, is there a downside to this? Well if you have never run a complete refresh on your materialized view, or created it with a "build immediate" clause, then you will not get the comfort of a STALENESS of "FRESH" and will instead be stuck with the disturbing "UNKNOWN". Relax -- it's just that although you know that the materialized view is fresh, Oracle does not trust you. But then there are so many events that can break the "FRESH" staleness of your materialized view that you should treat FRESH MV's as a rare treat and not get used to them.