• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1506
  • Last Modified:

Materialized View M$$ Index

I get an oracle error when I try to create the following index for a materialized view:  

SQL> CREATE UNIQUE INDEX I_SNAP$_LOAD_COST_CENTER_S ON LOAD_COST_CENTER_ST
  2  (M_ROW$$)
  3  LOGGING
  4  TABLESPACE MSP_LOAD_DATA
  5  NOPARALLEL;
(M_ROW$$)
 *
ERROR at line 2:
ORA-00904: invalid column name

The Materialized View is:

CREATE MATERIALIZED VIEW LOAD_COST_CENTER_ST
TABLESPACE MSP_LOAD_DATA
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE  
USING INDEX
REFRESH Complete with rowid
START WITH TO_DATE('10-May-2006 14:00:00','dd-mon-yyyy hh24:mi:ss')
NEXT (ROUND(SYSDATE+6,'DD')+840/1440)
ENABLE QUERY REWRITE
AS
SELECT
"COST_CENTER_ST"."CORP_ID" "CORP_CODE",
"COST_CENTER_ST"."PGRD_COST_CTR_ID" "COST_CENTER_ID",
"COST_CENTER_ST"."PGRD_COST_CTR_DESC" "COST_CENTER_SHORT_DESC",
"COST_CENTER_ST"."PGRD_COST_CTR_CONCAT_DESC" "COST_CENTER_LONG_DESC",
"COST_CENTER_ST"."PGRD_PHYS_SITE_ID" "RESEARCH_UNIT",
"COST_CENTER_ST"."PGRD_PHYS_SITE_DESC" "RESEARCH_UNIT_DESC",
"COST_CENTER_ST"."PGRD_EMPL_FULL_NAME" "DEPARTMENT_MANAGER",
"COST_CENTER_ST"."PGRD_ORG_ID" "COST_CENTER_ORG_ID",
"COST_CENTER_ST"."PGRD_ORG_DESC" "COST_CENTER_ORG_LONG_DESC",
"COST_CENTER_ST"."PGRD_FUNCTION_ID" "COST_CENTER_GRP_ORG_ID",
"COST_CENTER_ST"."PGRD_FUNCTION_DESC" "COST_CENTER_GRP_ORG_DESC",
"COST_CENTER_ST"."PGRD_GLOBAL_LINE_ID" "COST_CENTER_FUNCTION_ID",
"COST_CENTER_ST"."PGRD_GLOBAL_LINE_DESC" "COST_CENTER_FUNCTION_DESC",
"COST_CENTER_ST"."PGRD_ZONE_ID" "COST_CENTER_GRP_FUNCTION_ID",
"COST_CENTER_ST"."PGRD_ZONE_DESC" "COST_CENTER_GRP_FUNCTION_DESC",
"COST_CENTER_ST"."PGRD_PHYS_SITE_CNTRY_CODE" "COUNTRY_CODE",
"COST_CENTER_ST"."PHYS_CNTRY_NAME" "COUNTRY_SHORT_DESC",
"COST_CENTER_ST"."PHYS_CNTRY_DESC" "COUNTRY_LONG_DESC",
"COST_CENTER_ST"."PGRD_COST_CTR_ACTV_STATUS_IND" "ACTIVE_INACTIVE_IND",
"COST_CENTER_ST"."CREATE_DATE" "ROW_CREATION_DATE",
"COST_CENTER_ST"."PGRD_COST_CTR_NOTE" "NOTES",
"COST_CENTER_ST"."PGRD_PHYS_SITE_CODE" "RESEARCH_UNIT_SHORT_CODE",
"COST_CENTER_ST"."PGRD_MGNG_SITE_CODE" "MGMT_RESEARCH_UNIT_SHORT_CODE",
"COST_CENTER_ST"."PGRD_MGNG_SITE_ID" "MGMT_RESEARCH_UNIT",
"COST_CENTER_ST"."PGRD_MGNG_SITE_DESC" "MGMT_RESEARCH_UNIT_DESC",
"COST_CENTER_ST"."PGRD_MGNG_SITE_CNTRY_CODE" "MGMT_COUNTRY_CODE",
"COST_CENTER_ST"."MGNG_CNTRY_NAME" "MGMT_COUNTRY_SHORT_DESC",
"COST_CENTER_ST"."MGNG_CNTRY_DESC" "MGMT_COUNTRY_LONG_DESC",
"COST_CENTER_ST"."PGRD_COST_CTR_TYPE" "MANNING_TYPE_CODE",
"COST_CENTER_ST"."SRC_ID" "EXT_SYS_ID"
FROM "COST_CENTER_ST"@gifods_p.GROTON.PFIZER.COM "COST_CENTER_ST";

Any Clues???

Thanks Much.
0
dreamerw7
Asked:
dreamerw7
  • 8
  • 3
  • 3
1 Solution
 
paquicubaCommented:
M_ROW$$ is not a column in your MV
0
 
actonwangCommented:
M_ROW$$  is a hidden column

select M_ROW$$ from LOAD_COST_CENTER_ST where rownum<2
/


what do you see?
0
 
actonwangCommented:
>> M_ROW$$ is not a column in your MV

     M_ROW$$ will be generated as a hidden column if you create a materialized view "with rowid".
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
actonwangCommented:
>>USING INDEX
    by default, there should be an index created on M_ROW$$ already. I guess that sb dropped it? You could ask to see if that is the case.
0
 
dreamerw7Author Commented:
Yes, I thought if I used:

USING INDEX and WITH ROWID it would be there. I will check. Shouldn't the error be it already exists then?
0
 
actonwangCommented:
did you run my query to see if M_ROW$$ is still there?

I would execpt that there is an error saying "an index already in this column list" not "column not exist"...


acton
0
 
dreamerw7Author Commented:
select M_ROW$$ from LOAD_COST_CENTER_ST where rownum<2
/

I get ora:00904 invalid column name
0
 
paquicubaCommented:
Is not a valid column.

You may need to use DBMS_REDEFINITION
0
 
actonwangCommented:
>>I get ora:00904 invalid column name

    the column has been dropped.  you'd need to recreate this view to bring it back.

    I am curious who dropped it and why because this column should be existed to do fast refresh.
0
 
actonwangCommented:
>>You may need to use DBMS_REDEFINITION
    to paquicuba: No. you can not use it to bring M_ROW$$ back. It is only for TABLE online definition.
0
 
paquicubaCommented:
What do you need M_ROW$$ for?

Add a column name M_ROW$$ of ROWID type and update it with the new ROWIDs.
0
 
actonwangCommented:
>> What do you need M_ROW$$ for?

      ??? did you see my previous posts...
0
 
dreamerw7Author Commented:
Yes, I saw previous posts, but they don't seem to apply here??? the view was created with the code above and then an index on the M$$ "column". Noone has dropped anything. We just get the error and I am curious as to why.....thanks....
0
 
actonwangCommented:
what i am trying to say is that m_row$$ should be created and an index on it as well.

You could run the following simple script to see if that is true:
////

create table test_mrow(col number)
/

insert into test_mrow values(1)
/

insert into test_mrow values(2)
/

commit;

create materialized view mview_test
refresh complete  with rowid
as
select col from test_mrow
/

select m_row$$ from mview_test
/

drop table test_mrow
/

drop materialized view mview_test
/

0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 8
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now