Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DBMS_METADATA.GET_DDL and Materialized View

573141May 20 2009 — edited May 20 2009
hi gurus,

In 10gR2, i have created a materialized view EMP_MV with a simple structure.
I wanted to get the DDL of EMP_MV.

I used DBMS_METADATA to get the DDL,
select DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','EMP_MV','TARGET') into v_return from dual;

Output

CREATE MATERIALIZED VIEW "TARGET"."EMP_MV"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TARGET_TS_01"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TARGET_TS_01"
REFRESH FAST ON DEMAND
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS select empid,empname,address from web.emp


Is there any way to get only SELECT part of this MV, ie select empid,empname,address from web.emp directly from dictionary without any string manipulation?

tia,
newbie
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 17 2009
Added on May 20 2009
2 comments
13,473 views