asked on

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.

paquicuba

M_ROW$$ is not a column in your MV

ASKER CERTIFIED SOLUTION

Acton Wang

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Acton Wang

>> 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".

Acton Wang

>>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.

dreamerw7

ASKER

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?

Acton Wang

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

dreamerw7

ASKER

select M_ROW$$ from LOAD_COST_CENTER_ST where rownum<2
/

I get ora:00904 invalid column name

paquicuba

Is not a valid column.

You may need to use DBMS_REDEFINITION

Acton Wang

>>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.

Acton Wang

>>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.

paquicuba

What do you need M_ROW$$ for?

Add a column name M_ROW$$ of ROWID type and update it with the new ROWIDs.

Acton Wang

>> What do you need M_ROW$$ for?

??? did you see my previous posts...

dreamerw7

ASKER

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....

Acton Wang

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
/