sql - 从物化 View DDL 中删除表空间信息

Question

使用以下 SQL，可以获得给定物化 View 的 DDL。

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

我很难在没有表空间信息的情况下检索 DDL。 SET_TRANSFORM_PARAM指令实际上被记录为特定于表和索引(不是物化 View )。 STORAGE一个确实有效，而 TABLESPACE和 SEGMENT_ATTRIBUTES没有效果。有没有办法从生成的 DDL 中省略表空间信息？

Answer 1

您需要将调用 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