• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1597
  • 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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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