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

发布时间：2020-12-12 06:38:22 所属栏目：MsSql教程来源：网络整理

导读：使用以下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;

使用以下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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!