Home » SQL & PL/SQL » SQL & PL/SQL » ORA-12043: invalid CREATE MATERIALIZED VIEW option (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production)
ORA-12043: invalid CREATE MATERIALIZED VIEW option [message #347576] Fri, 12 September 2008 04:02 Go to next message
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 #347577 is a reply to message #347576] Fri, 12 September 2008 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-12043: invalid CREATE MATERIALIZED VIEW option
 *Cause:  An invalid option was used in a CREATE MATERIALIZED VIEW statement.
 *Action: Specify only valid options.

Check syntax in SQL Reference.

Regards
Michel
Re: ORA-12043: invalid CREATE MATERIALIZED VIEW option [message #347585 is a reply to message #347577] Fri, 12 September 2008 04:34 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
The SQL reference is correct.

I took an extract of the DDL of the existing MV from QA, then i dropped the MV and tried recreating it, surprisingly it throwed up the same error !! I ran the DDL extract, how can this happen ?? I am surprised !!
Re: ORA-12043: invalid CREATE MATERIALIZED VIEW option [message #347589 is a reply to message #347585] Fri, 12 September 2008 04:49 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
is the
NOMONITORING
option valid in ORA 9.2?
Re: ORA-12043: invalid CREATE MATERIALIZED VIEW option [message #347591 is a reply to message #347585] Fri, 12 September 2008 04:56 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Thanks buddy, that was the problem !! Smile
Re: ORA-12043: invalid CREATE MATERIALIZED VIEW option [message #347593 is a reply to message #347591] Fri, 12 September 2008 04:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Does this mean you have a different database-version on QA then on Dev?!
Re: ORA-12043: invalid CREATE MATERIALIZED VIEW option [message #347594 is a reply to message #347585] Fri, 12 September 2008 05:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The error is caused by the NOMONITORING option
Re: ORA-12043: invalid CREATE MATERIALIZED VIEW option [message #347596 is a reply to message #347594] Fri, 12 September 2008 05:05 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
All 3 have same versions !!

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

But i still dont get it, why 'NOMONITORING' option came out from the extract of the DDL or is it a bug in Oracle !! Smile Laughing
Re: ORA-12043: invalid CREATE MATERIALIZED VIEW option [message #347599 is a reply to message #347596] Fri, 12 September 2008 05:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What did you use to generate the DDL for this view.
I've just used DBMS_METADATA on 10g, and that doesn't include the Nomonitoring option.
Re: ORA-12043: invalid CREATE MATERIALIZED VIEW option [message #347602 is a reply to message #347599] Fri, 12 September 2008 05:28 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
I used SQL navigator 5.5.2
Re: ORA-12043: invalid CREATE MATERIALIZED VIEW option [message #347604 is a reply to message #347602] Fri, 12 September 2008 05:32 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
I have no idea how to extract DDL from metadata, can you please show me
Re: ORA-12043: invalid CREATE MATERIALIZED VIEW option [message #347605 is a reply to message #347604] Fri, 12 September 2008 05:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
jagannathkiran wrote on Fri, 12 September 2008 12:32
I have no idea how to extract DDL from metadata, can you please show me

JRowbottom wrote on Fri, 12 September 2008 12:25

I've just used DBMS_METADATA on 10g,

Did you google for dbms_metadata?

[Updated on: Fri, 12 September 2008 05:40]

Report message to a moderator

Re: ORA-12043: invalid CREATE MATERIALIZED VIEW option [message #347613 is a reply to message #347602] Fri, 12 September 2008 05:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I stongly suspect that there is a bug in SQL Navigator then.
NoMonitoring is an option that applies to tables - I reckon it is incorrectly including that with the DDL for a MV.

DBMS_METADATA

Example:
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MEMO_MV','ADM') from dual;
Re: ORA-12043: invalid CREATE MATERIALIZED VIEW option [message #347614 is a reply to message #347613] Fri, 12 September 2008 06:07 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Even I guess so, maybe i will extract using dbms_metadata and verify the same , hold on
Re: ORA-12043: invalid CREATE MATERIALIZED VIEW option [message #347615 is a reply to message #347614] Fri, 12 September 2008 06:19 Go to previous message
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 )

  

Previous Topic: exiting plsql block
Next Topic: DFF
Goto Forum:
  


Current Time: Fri Dec 09 21:34:41 CST 2016

Total time taken to generate the page: 0.24395 seconds