Here is the output from the SQLPlus session:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> create table gcust.abc
2 (col1 varchar2(10) not null,
3 col2 varchar2(10) not null,
4 col3 varchar2(10) not null);

Table created.

SQL> create materialized view gcust.mv_abc1
2 refresh on demand with rowid
3 as
4 select distinct col1, col2, col3, user, sysdate from gcust.abc;

Materialized view created.

SQL> set pagesize 0
SQL> set linesize 180
SQL> set long 90000
SQL>
SQL> SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_ABC1','GCUST') FROM dual;

CREATE MATERIALIZED VIEW "GCUST"."MV_ABC1"
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 "TS_CUST_DATA0"
BUILD IMMEDIATE
USING INDEX
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS select distinct col1, col2, col3, user, sysdate from gcust.abc

================

As you can see it does not show rowid!

Further, I created a materialized view that I know is syntactically correct and ran the DBS_METADATA.GET_DDL and it shows rowid, here are the results:


SQL> create materialized view gcust.mv_abc2
2 refresh on demand with rowid
3 as
4 select col1, col2, col3 from gcust.abc;

Materialized view created.

SQL>
SQL> SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_ABC2','GCUST') FROM dual;

CREATE MATERIALIZED VIEW "GCUST"."MV_ABC2"
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 "TS_CUST_DATA0"
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 "TS_CUST_DATA0"
REFRESH FORCE ON DEMAND
WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS select col1, col2, col3 from gcust.abc

====================================

Any ideas/information that you have would be greatly appreciated!

Thanks,
Pat