ORA-32321

Going through the site and feed stats, I found the best way to attract traffic is to talk about an error message.
So here’s the shameless discussion of ORA-32321.
According to OraDoc,

ORA-32321 REFRESH FAST of “string“.”string” unsupported after detail table TRUNCATE
Cause: A detail table has been truncated and no materialized view supports fast refersh after a detail table has been truncated.
Action: Use REFRESH COMPLETE. Note: you can determine why your materialized view does not support fast refresh after TRUNCATE using the DBMS_MVIEW.EXPLAIN_MVIEW() API.

What this really means is when you truncate a detail table on which a fast refresh MV bases, you need to do a complete refresh before any DML to the detail table and resuming usual fast refresh mode for the MV.
begin
execute immediate ‘truncate table detail_tab’;
— fast_MV is a fast refreshable MV built upon detail_tab
dbms_mview.refresh(‘fast_MV’, ‘c‘);
— do some DML to detail_tab, this is where ORA-32321 gets thrown if a complete refresh is not performed on fast_MV as the previous step.
insert/update/delete/merge…
— resume the usual fast refresh
dbms_mview.refresh(‘fast_MV’, ‘f’);
end;
/

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s


%d bloggers like this: