ORA-12052: cannot fast refresh materialized view

ORA-12052: cannot fast refresh materialized view

Error
Materialized view not doing a fast refresh. Complete refresh is working for this but Fast refresh is giving following error.
The materialized view is based on other materialized views. Complete refresh is working fine as show below:

-- Complete refresh
EXEC DBMS_MVIEW.REFRESH('SCOTT.EMPLOYEE', 'C', '', FALSE, FALSE, 0, 0,0, FALSE);
PL/SQL procedure successfully completed.

--- Fast refresh
SQL> EXEC DBMS_MVIEW.REFRESH('SCOTT.EMPLOYEE', 'F', '', FALSE, FALSE, 0, 0,0, FALSE);
BEGIN DBMS_MVIEW.REFRESH('SCOTT.EMPLOYEE', 'F', '', FALSE, FALSE, 0, 0,0, FALSE); END;

*
ERROR at line 1:
ORA-12052: cannot fast refresh materialized view SCOTT.EMPLOYEE
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
ORA-06512: at line 1

Solution
1. Create the mlog$ table for fast refresh mlog$ is require. so next step is that create mlog$ on table involve in this mview.

CREATE MATERIALIZED VIEW LOG ON scott.employee
TABLESPACE users
WITH PRIMARY KEY
INCLUDING NEW VALUES;

2. Check the MVIEW Capabilities and fixed the errors by generating following reports:

SET pages 56 lines 130 feedback on echo off long 1000000
REM
COLUMN capability_name format a30 heading "Capability|Name"
COLUMN possible format a10 heading "Possible"
COLUMN msgtxt format a70 heading "Message|Text"
REM
TTITLE "Materialized View Capabilities Report"
REM
DROP TABLE mv_capabilities_table;
@?/rdbms/admin/utlxmv.sql ;
EXEC dbms_mview.explain_mview('&&ENTER_OWNER..&&ENTER_MVIEW_NAME');
SELECT capability_name, possible, SUBSTR (msgtxt, 1, 60) AS msgtxt
FROM mv_capabilities_table;

3. Report from steps 2:

Capability Message
Name Possible Text
------------------------------ ---------- ----------------------------------------------------------------------
PCT N
REFRESH_COMPLETE Y <----- it say only possibility is Complete refresh
REFRESH_FAST N
REWRITE N
PCT_TABLE N Oracle error: see RELATED_NUM and RELATED_TEXT for details
PCT_TABLE N relation is not a partitioned table
PCT_TABLE N relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT N the SELECT list does not have the rowids of all the detail t <-----
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the mater

4. As per report only complete is possible with this materialized view.
If you add rowid also in this mview select statement and then only MV able to refresh it with fast.

Change code with added rowid column

Related:
Check refresh data for MVIEW

select
mview_name,
last_refresh_type,
to_char(last_refresh_date, 'DD-MON-YYYY HH24:MI:SS')
from
user_mviews
where
mview_name like 'CHICK%'
order by last_refresh_date;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.