Solved

Oracle snapshot refresh problem

Posted on 2010-11-18
4
1,217 Views
Last Modified: 2012-08-14
Hi,
When we refresh snapshots using dbms_refresh.refresh stored procedure, it always fails at a table "AXIUM"."ODLIMITS" with the following error message:
     ERROR: ORA-32320: REFRESH FAST of "AXIUM"."ODLIMITS" unsupported after container table PMOPs.
After I dropped/re-created both snapshot log in database A and snapshot in database B, the refresh can work for about 1 day, and then fails again after 1day.
The following are the commands to create snapshots and snapshot logs:
(1)In database A: create materialized view log on <tablename> tablespace <tablespacename>;
(2)In database B: create materialized view <tablename> on prebuilt table
        refresh fast
        start with sysdate + 1/288
        enable query rewrite
        as select * from <tablename>@prod;

Thanks a lot for your help,
Luchuan



0
Comment
Question by:luchuanc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 34168242
According to the documentation, that would mean some sort of partition maintenance operation has occurred.  Try using the procedure call listed in the actions to see what was done.


ORA-32320:
REFRESH FAST of "string"."string" unsupported after container table PMOPs
Cause:       A Partition Maintenance Operation (PMOP) has been performed on the materialized view, and no materialized view supports fast refersh after container table PMOPs.
Action:       Use REFRESH COMPLETE. Note: you can determine why your materialized view does not support fast refresh after PMOPs using the DBMS_MVIEW.EXPLAIN_MVIEW() API.
0
 

Author Comment

by:luchuanc
ID: 34168696
Hi johnsone:,
I ran DBMS_MVIEW.EXPLAIN_MVIEW() for this snapshot and another snapshot that has no problem, did comparison. Results look similar for them in mV_CAPABILITIES_TABLE. This snapshot was created using the same command for other snapshots that are all fine.
Thanks,

Luchuan
0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 34172958
According to the message, it has nothing to do with how the snapshot was created.  It has to do with some partition maintenance that was done.
0
 

Author Closing Comment

by:luchuanc
ID: 34219149
Hi,
I ran a refresh complete command in database, and that seems solved problem.
Thanks for your help
Luchuan
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question