mysql 物化视图 知乎_如何获取物化视图的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

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