DBMS_METADATA.GET_DDL and Materialized View
573141May 20 2009 — edited May 20 2009hi 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