ORA-12043: invalid CREATE MATERIALIZED VIEW option [message #347576] |
Fri, 12 September 2008 04:02  |
NewLife
Messages: 170 Registered: April 2008
|
Senior Member |
|
|
I have created the following MV in my QA and DEV databases, and when i am trying to do the same in Production, it is showing the following error:
14:28:14 ORA-12043: invalid CREATE MATERIALIZED VIEW option
CREATE MATERIALIZED VIEW adm.memo_mv
PCTFREE 10
PCTUSED 40
MAXTRANS 255
TABLESPACE v40usr1
STORAGE (
INITIAL 131072
NEXT 1048576
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
NOMONITORING
NOLOGGING
BUILD IMMEDIATE
REFRESH ON DEMAND
AS
SELECT
distinct pr.proj as Project_Name,
pr.ds as Project_Description,
av.act as Activity_Name,
av.ds as Activity_Description,
av.obs as Activity_OBS,
av.memo_sent as Memo_sent,
av.uc04 as Responsible_person,
av.uc03 as Report_or_Study_No,
av.uc06 as Tst_Subst_code,
pr.pc08 as Project_leader,
av.pi as pi,
av.ver as ver,
av.eid as Start_Date,
av.lid as End_Date,
av.act_due_date as Reg_Due_Date,
av.act_res_status as Activity_status
FROM
av_activity av,
av_project pr
WHERE
pr.proj <> 'ADMIN-99' and
pr.pc15 in ('ACTIVE','NOT ACTIVE','HOLD') and
av.proj = pr.proj and
av.memo_sent between 11 and 19 and
av.ver in ( 0,97 )
/
Note* All 3 databases are Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
Any clue why this might be happening ?
[Updated on: Fri, 12 September 2008 04:05] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: ORA-12043: invalid CREATE MATERIALIZED VIEW option [message #347615 is a reply to message #347614] |
Fri, 12 September 2008 06:19  |
NewLife
Messages: 170 Registered: April 2008
|
Senior Member |
|
|
Yeah i guess there is a bug in SQL nav 5.5.2.742, it is obvious from below:
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MEMO_MV','ADM') from dual;
Here you go:
DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MEMO_MV','ADM')
----------------------------------------------------------
CREATE MATERIALIZED VIEW "ADM"."MEMO_MV"
ORGANIZATION HEAP
PCTFREE 10
PCTUSED 40
INITRANS 2
MAXTRANS 255
NOCOMPRESS
NOLOGGING
STORAGE(
INITIAL 131072
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "V40USR1"
BUILD IMMEDIATE
USING INDEX
REFRESH FORCE ON DEMAND NEXT null
WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT
distinct pr.proj as Project_Name,
pr.ds as Project_Description,
av.act as Activity_Name,
av.ds as Activity_Description,
av.obs as Activity_OBS,
av.memo_sent as Memo_sent,
av.uc04 as Responsible_person,
av.uc03 as Report_or_Study_No,
av.uc06 as Tst_Subst_code,
pr.pc08 as Project_leader,
av.pi as pi,
av.ver as ver,
av.eid as Start_Date,
av.lid as End_Date,
av.act_due_date as Reg_Due_Date,
av.act_res_status as Activity_status
FROM
av_activity av,
av_project pr
WHERE
pr.proj <> 'ADMIN-99' and
pr.pc15 in ('ACTIVE','NOT ACTIVE','HOLD') and
av.proj = pr.proj and
av.memo_sent between 11 and 19 and
av.ver in ( 0,97 )
|
|
|