asked on

Oracle snapshot refresh problem

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

johnsone

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.

luchuanc

ASKER

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

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

luchuanc

ASKER

Hi,
I ran a refresh complete command in database, and that seems solved problem.
Thanks for your help
Luchuan