Remove tablespace info from Materialized View DDL

Question

Using the following SQL, the DDL for a given materialized view can be obtained.

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;

I am having difficulty retrieving the DDL without the tablespace information. The SET_TRANSFORM_PARAM directives are actually documented as being specific to tables and indexes (not materialized views). The STORAGE one does actually work whereas the TABLESPACE and SEGMENT_ATTRIBUTES ones have no effect. Is there any way to omit the tablespace info from the generated DDL?

Where are you running the code and what does the output look like? — Tom H, Aug 18 '11 at 14:18

score 6 · Accepted Answer · answered Aug 18 '11 at 14:57

You need to set the object_type in the calls to SET_TRANSFORM_PARAM to MATERIALIZED_VIEW. In my example below, the tablespace is no mentioned:

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

Remove tablespace info from Materialized View DDL

1 Answers1