sql – 从Materialized View DDL中删除表空间信息

使用以下sql,可以获取给定物化视图的DDL.

BEGIN
    DBMS_MetaDATA.SET_TRANSFORM_PARAM(DBMS_MetaDATA.SESSION_TRANSFORM,'STORAGE',FALSE);
    DBMS_MetaDATA.SET_TRANSFORM_PARAM(DBMS_MetaDATA.SESSION_TRANSFORM,'TABLESPACE','SEGMENT_ATTRIBUTES',FALSE);
END;
SELECT DBMS_MetaDATA.GET_DDL('MATERIALIZED_VIEW','OBJECT_NAME','SCHEMA_NAME') FROM DUAL;

我在检测没有表空间信息的DDL时遇到困难.实际上,SET_TRANSFORM_PARAM指令被记录为特定于表和索引(不是物化视图). STORAGE实际上工作,而TABLESPACE和SEGMENT_ATTRIBUTES没有效果.有没有办法从生成的DDL中省略表空间信息？

解决方法

您需要将对SET_TRANSFORM_PARAM的调用中的object_type设置为MATERIALIZED_VIEW.在下面的示例中,没有提到表空间：

create materialized view mv
as select * from large_t where rownum < 100;

begin DBMS_MetaDATA.SET_TRANSFORM_PARAM (
              transform_handle => dbms_Metadata.session_transform,name             => 'TABLESPACE',value            => false,object_type      => 'MATERIALIZED_VIEW');
end;
/

select dbms_Metadata.get_ddl(
           'MATERIALIZED_VIEW','MV',user)
         from dual;

CREATE MATERIALIZED VIEW "SODONNEL"."MV" ("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPACE","EDITION_NAME")
  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)
  BUILD IMMEDIATE
  USING INDEX 
  REFRESH FORCE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS select * from large_t where rownum < 100

sql – 从Materialized View DDL中删除表空间信息

解决方法

猜你在找的MsSQL相关文章