如何获取物化视图的ddl语句呢

郑全

我创建了一个物化视图,如下,时间久了,忘了物化视图的定义了 ,在哪里去找呢:

CREATE MATERIALIZED VIEW cust_sales_mv
ENABLE QUERY REWRITE AS
SELECT c.cust_id, SUM(amount_sold)
FROM   sales s, customers c
WHERE  s.cust_id = c.cust_id
GROUP BY c.cust_id;

我通过select object_name,object_type from dba_objects where object_name='CUST_SALES_MV';


SQL> select object_name,object_type from dba_objects where object_name='CUST_SALES_MV';
OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
CUST_SALES_MV                  TABLE
CUST_SALES_MV                  MATERIALIZED VIEW

在通过get_ddl语句报类型不对:

SQL> SELECT dbms_metadata .get_ddl('MATERIALIZED VIEW','CUST_SALES_MV','SH')
  2  from dual;
ERROR:
ORA-31600: invalid input value MATERIALIZED VIEW for parameter OBJECT_TYPE in function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 1

no rows selected

看来不能直接使用 'MATERIALIZED VIEW',怎么办呢
中间加上_试试:

SQL> SELECT dbms_metadata .get_ddl('MATERIALIZED_VIEW','CUST_SALES_MV','SH')
  2  from dual ;
DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','CUST_SALES_MV','SH')
--------------------------------------------------------------------------------
  CREATE MATERIALIZED VIEW "SH"."CUST_SALES_MV" ("CUST_ID", "SUM(AMOUNT_SOLD)")
  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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  BUILD IMMEDIATE
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','CUST_SALES_MV','SH')
--------------------------------------------------------------------------------
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  REFRESH FORCE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS ENABLE QUERY REWRITE
  AS SELECT c.cust_id, SUM(amount_sold)
FROM   sales s, customers c
WHERE  s.cust_id = c.cust_id
GROUP BY c.cust_id


出来了,看来对象类型中间不能有空格.