OraFAQ Forum: SQL & PL/SQL » DESC MATERIALIZED VIEW

Home » SQL & PL/SQL » SQL & PL/SQL » DESC MATERIALIZED VIEW (Oracle 10g Enterprise Edition Release 10.2.0.3.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DESC MATERIALIZED VIEW [message #340423]

Tue, 12 August 2008 16:02

sriramkgg
Messages: 12
Registered: July 2007

Junior Member

Hello,

I created a materialized view. But now when I try to see the script of it using TOAD(version 7.3.0.0) it shows the create table script.

When I use SQL*Plus and use the DBMS_METADATA.GET_DDL to see the script it throws the ORA-31603 error which says object of type materialized view with this name does not exist.

Is it a privilege issue? But in that case it should not allow me to create the materialized view, let alone desccribe it.

What could be the reason.

Thanks
Ram

Report message to a moderator

Re: DESC MATERIALIZED VIEW [message #340427 is a reply to message #340423]

Tue, 12 August 2008 16:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Here is what we know.
1) You think you have a problem.
2) You have more questions than answers.
3) You have provided us no useful FACTS to answer your questions.
4) We were not standing behind & did not see what you did or how Oracle responded.

We don't know for sure if the materialized view really exists or is owned by the user/schema which you logged in as.

Good Luck at obtaining meaningful answers.

You're On Your Own (YOYO)!

Report message to a moderator

Re: DESC MATERIALIZED VIEW [message #340432 is a reply to message #340423]

Tue, 12 August 2008 17:31

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

You are probably attempting to reference the name of the materialized view in a case (upper or lower) that is different from how it was created, as shown below.

-- object created without specifying case defaults to upper case and must be referenced in upper case:

SCOTT@orcl_11g> CREATE MATERIALIZED VIEW your_mview AS SELECT * FROM dept
  2  /

Materialized view created.

SCOTT@orcl_11g> SELECT DBMS_METADATA.GET_DDL ('MATERIALIZED_VIEW', 'your_mview') FROM DUAL
  2  /
ERROR:
ORA-31603: object "your_mview" of type MATERIALIZED_VIEW not found in schema
"SCOTT"
ORA-06512: at "SYS.DBMS_METADATA", line 1968
ORA-06512: at "SYS.DBMS_METADATA", line 2011
ORA-06512: at "SYS.DBMS_METADATA", line 2343
ORA-06512: at "SYS.DBMS_METADATA", line 3247
ORA-06512: at "SYS.DBMS_METADATA", line 4863
ORA-06512: at line 1



no rows selected

SCOTT@orcl_11g> SELECT DBMS_METADATA.GET_DDL ('MATERIALIZED_VIEW', 'YOUR_MVIEW') FROM DUAL
  2  /

DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','YOUR_MVIEW')
--------------------------------------------------------------------------------

  CREATE MATERIALIZED VIEW "SCOTT"."YOUR_MVIEW"
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOG
GING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FORCE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS SELECT * FROM dept



1 row selected.

-- object created in lower case using double quotes (not a good
practice) must be referenced in lower case:

SCOTT@orcl_11g> CREATE MATERIALIZED VIEW "another_mview" AS SELECT * FROM dept
  2  /

Materialized view created.

SCOTT@orcl_11g> SELECT DBMS_METADATA.GET_DDL ('MATERIALIZED_VIEW', 'ANOTHER_MVIEW') FROM DUAL
  2  /
ERROR:
ORA-31603: object "ANOTHER_MVIEW" of type MATERIALIZED_VIEW not found in schema
"SCOTT"
ORA-06512: at "SYS.DBMS_METADATA", line 1968
ORA-06512: at "SYS.DBMS_METADATA", line 2011
ORA-06512: at "SYS.DBMS_METADATA", line 2343
ORA-06512: at "SYS.DBMS_METADATA", line 3247
ORA-06512: at "SYS.DBMS_METADATA", line 4863
ORA-06512: at line 1



no rows selected

SCOTT@orcl_11g> SELECT DBMS_METADATA.GET_DDL ('MATERIALIZED_VIEW', 'another_mview') FROM DUAL
  2  /

DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','ANOTHER_MVIEW')
--------------------------------------------------------------------------------

  CREATE MATERIALIZED VIEW "SCOTT"."another_mview"
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOG
GING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FORCE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS SELECT * FROM dept



1 row selected.

SCOTT@orcl_11g>

Report message to a moderator

Re: DESC MATERIALIZED VIEW [message #340628 is a reply to message #340432]

Wed, 13 August 2008 08:33

sriramkgg
Messages: 12
Registered: July 2007

Junior Member

Hi Barbara,

Thank you very much.

That was exactly the problem. I never thought case sensitivity was the issue here.

Once again thanks a lot. Much appreciated.

Regards
Ram

Report message to a moderator

Previous Topic:	SUBTOTALS for the repeating row.
Next Topic:	PL/SQL Loop

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 20 10:39:34 CDT 2026