Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01410 Invalid Rowid while selecting/refreshing Materialized Views (Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi, Unix)
ORA-01410 Invalid Rowid while selecting/refreshing Materialized Views [message #572804] Mon, 17 December 2012 14:58 Go to next message
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 #572828 is a reply to message #572804] Tue, 18 December 2012 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 58934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Distinct and rowid are exclusive. If you select distinct row values, then you lose the rowids.

Post the EXACT and COMPLETE mview creation statement (without storage clause).

Regards
Michel
Re: ORA-01410 Invalid Rowid while selecting/refreshing Materialized Views [message #572906 is a reply to message #572828] Tue, 18 December 2012 09:39 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: ORA-03113 end-of-file on communication channel
Next Topic: oracle 11g issue
Goto Forum:
  


Current Time: Fri Aug 29 07:38:48 CDT 2014

Total time taken to generate the page: 0.17617 seconds