Hi Tom,Can you please help me in the below issue Please?
I have code that is causing an issue during run time,but i dont know how to write exception handling for the same.
Ex. There is a view(v1) on a table and another view(v2) in which has some business logic.There is a MV in which V! and V2 are being referred and when im trying to refresh this MV its failing with and error
ORA-12008: error in materialized view or zonemap refresh path
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2432
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 253
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2413
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2976
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3263
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3295
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
ORA-06512: at "DWCOMMON.DWANALYTICS_PKG", line 2395
ORA-06512: at line 1
ORA-06512: at "INTERFACE_ORCH.EXECUTE_DAILY_JOBS_PKG", line 24
I really dont get what is cause of failure as this doesn't include root cause of the error.
The same error message i get for any MV refresh failure.
ORA-12008: error in materialized view or zonemap refresh path
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2432
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 253
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2413
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2976
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3263
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3295
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
ORA-06512: at "DWCOMMON.DWCOMMON_PKG", line 2344
ORA-06512: at line 1
ORA-06512: at "INTERFACE_ORCH.EXECUTE_DAILY_JOBS_PKG", line 24
It will allways gives me the same generic Error
As you say, this a fairly generic error.
Things to check:
- What is the query in the MV? Do you get any errors when running this directly (i.e. NOT in the MV)?
- Are you attempting to fast refresh for the MV? If so, is there a materialized view log on all the underlying tables?
- Pass it to dbms_mview.explain_mview to see what types of refresh are possible:
/*
-- create this table if not present
create table mv_capabilities_table (
statement_id varchar(30) ,
mvowner varchar(30) ,
mvname varchar(30) ,
capability_name varchar(30) ,
possible character(1) ,
related_text varchar(2000) ,
related_num number ,
msgno integer ,
msgtxt varchar(2000) ,
seq number
);
truncate table mv_capabilities_table ;
*/
exec dbms_mview.explain_mview('YOUR_MV');
select capability_name, possible, msgtxt from mv_capabilities_table ;