| ORA-01410 Invalid Rowid while selecting/refreshing Materialized Views [message #572804] |
Mon, 17 December 2012 14:58  |
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
|
Member |
|
|
Hi
I have a Multi layer Mviews and Views which i use to load a table.
there are three base level Mview which is accessed by 4 views ... which are in turn accessed by a view which is used to load a table.
Before the insert called for the table .. the base mviews are refreshed.
I get the "ORA-01410 Invalid Rowid" error while inserting in to the table and while refreshing the mviews....
They dont occur every time and not together as well.
The Mviews contains table joins and the top level views which accessing these mviews does aggregation.
Below is the mview query for one of the mview.
SELECT DISTINCT c.fiscal_time_id,
a.product_group_id,
a.allocation_driver_id,
a.hyp_entity_id,
d.business_division_alt_id
FROM edw.edw_mnth_aloc_drv_fct a,
dim.edw_fiscal_time_dim t ,
dim.edw_fiscal_time_dim c ,
dim.edw_hyp_entity_dim h ,
dim.edw_business_division_dim d
WHERE a.fiscal_month_id = t.fiscal_time_id
AND t.fiscal_time_level_nme ='MONTH'
AND (t.fiscal_month_end_dt BETWEEN add_months(sysdate,-13) AND sysdate)
AND c.fiscal_time_level_nme ='MONTH'
AND (c.fiscal_month_end_dt BETWEEN add_months(sysdate,-13) AND sysdate)
AND a.hyp_entity_id = h.hyp_entity_id
AND h.edw_current_flg ='Y'
AND h.business_division_cd = d.business_division_alt_cd
AND d.edw_current_flg ='Y'
The Refresh method for the mview is Force.
Please help me to solve this issue.
|
|
|
|
|
|
| Re: ORA-01410 Invalid Rowid while selecting/refreshing Materialized Views [message #572906 is a reply to message #572828] |
Tue, 18 December 2012 09:39  |
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
|
Member |
|
|
HI Michel,
Please find the complete DDL for the Mview below
CREATE MATERIALIZED VIEW "STG"."EX_VALID_LIST_ALLOC_MV"
("FISCAL_TIME_ID", "PRODUCT_GROUP_ID", "ALLOCATION_DRIVER_ID", "HYP_ENTITY_ID", "BUSINESS_DIVISION_ALT_ID")
BUILD IMMEDIATE USING INDEX REFRESH FORCE ON DEMAND USING DEFAULT LOCAL ROLLBACK SEGMENT DISABLE QUERY REWRITE
AS
SELECT DISTINCT c.fiscal_time_id,
a.product_group_id,
a.allocation_driver_id,
a.hyp_entity_id,
d.business_division_alt_id
FROM edw.edw_mnth_aloc_drv_fct a,
dim.edw_fiscal_time_dim t ,
dim.edw_fiscal_time_dim c ,
dim.edw_hyp_entity_dim h ,
dim.edw_business_division_dim d
WHERE a.fiscal_month_id = t.fiscal_time_id
AND t.fiscal_time_level_nme ='MONTH'
AND (t.fiscal_month_end_dt BETWEEN add_months(sysdate,-13) AND sysdate)
AND c.fiscal_time_level_nme ='MONTH'
AND (c.fiscal_month_end_dt BETWEEN add_months(sysdate,-13) AND sysdate)
AND a.hyp_entity_id = h.hyp_entity_id
AND h.edw_current_flg ='Y'
AND h.business_division_cd = d.business_division_alt_cd
AND d.edw_current_flg ='Y';
[Updated on: Tue, 18 December 2012 10:05] by Moderator Report message to a moderator |
|
|
|
| Re: ORA-01410 Invalid Rowid while selecting/refreshing Materialized Views [message #572908 is a reply to message #572906] |
Tue, 18 December 2012 09:46 |
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
|
Member |
|
|
This is one of the 4 base mviews ... there is a three mid level views which refers to these mviews and one final top level view which uses the mid level views. Its a hierarchy of mview and view which is used in the insert statement.
On few occasions i get the invalid row id error in two instances ..
1) while refreshing the base mviews
2) during multiple instance of the insert statements ( insert into table as select from view )
I am trying to understand why this is happening and to find a solution to mitigate it .
Thanks
Arun
[Updated on: Tue, 18 December 2012 09:46] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
