Solved

Oracle snapshot refresh problem

Posted on 2010-11-18
4
1,162 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
  • 2
  • 2
4 Comments
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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
Comment Utility
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 34

Accepted Solution

by:
johnsone earned 500 total points
Comment Utility
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
Comment Utility
Hi,
I ran a refresh complete command in database, and that seems solved problem.
Thanks for your help
Luchuan
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now