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