Home » SQL & PL/SQL » SQL & PL/SQL » DESC MATERIALIZED VIEW (Oracle 10g Enterprise Edition Release 10.2.0.3.0)
DESC MATERIALIZED VIEW [message #340423] Tue, 12 August 2008 16:02 Go to next message
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

Re: DESC MATERIALIZED VIEW [message #340427 is a reply to message #340423] Tue, 12 August 2008 16:13 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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)!
Re: DESC MATERIALIZED VIEW [message #340432 is a reply to message #340423] Tue, 12 August 2008 17:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
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> 

Re: DESC MATERIALIZED VIEW [message #340628 is a reply to message #340432] Wed, 13 August 2008 08:33 Go to previous message
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
Previous Topic: SUBTOTALS for the repeating row.
Next Topic: PL/SQL Loop
Goto Forum:
  


Current Time: Wed Dec 07 12:23:57 CST 2016

Total time taken to generate the page: 0.11062 seconds