DBMS_METADATA.SET_TRANSFORM_PARAM not able to suppress the tablespace physical attributes, storage attributes using DBMS_METADATA.GET_DDL for MATERIALIZED_VIEW or Some other objects

(Doc ID 1369550.1)

Last updated on DECEMBER 30, 2015

Applies to:

PL/SQL - Version 10.2.0.4 to 11.1.0.7 [Release 10.2 to 11.1]
Oracle Applications Technology Stack - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1]
Oracle Database - Enterprise Edition - Version 10.2.0.4 to 11.1.0.7 [Release 10.2 to 11.1]
Information in this document applies to any platform.

Symptoms


EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',FALSE);

EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);

Using this two commands not able to suppress the tablespace physical attributes, storage attributes, tablespace, logging information while extracting the object using DBMS_METADA.GET_DDL for MATERIALIZED_VIEW or some other object types

Changes


Simple test case with only 2 columns

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> GRANT DBA TO SRITEST IDENTIFIED BY A;

Grant succeeded.

SQL> CONN SRITEST/A;
Connected.
SQL> CREATE TABLE tab1(col1 NUMBER PRIMARY KEY,col2 VARCHAR2(20));

Table created.

SQL> CREATE MATERIALIZED VIEW mv1
2 BUILD IMMEDIATE
3 USING INDEX
4 REFRESH COMPLETE ON DEMAND
5 USING DEFAULT LOCAL ROLLBACK SEGMENT
6 DISABLE QUERY REWRITE
7 AS SELECT * FROM tab1;

Materialized view created.

SQL> EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',TRUE);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES', TRUE);

PL/SQL procedure successfully completed.

SQL> set long 200000 line 1000 pages 200
SQL> SELECT DBMS_METADATA.GET_DDL ('TABLE','TAB1','SRITEST') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','TAB1','SRITEST')
--------------------------------------------------------------------------------

CREATE TABLE "SRITEST"."TAB1"
( "COL1" NUMBER,
"COL2" VARCHAR2(20),
PRIMARY KEY ("COL1")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"


SQL> SELECT DBMS_METADATA.GET_DDL ('MATERIALIZED_VIEW','MV1','SRITEST') FROM DUAL;

DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV1','SRITEST')
--------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW "SRITEST"."MV1" ("COL1", "COL2")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS SELECT "TAB1"."COL1" "COL1","TAB1"."COL2" "COL2" FROM "TAB1" "TAB1"


SQL> EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES', FALSE);

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_METADATA.GET_DDL ('TABLE','TAB1','SRITEST') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','TAB1','SRITEST')
--------------------------------------------------------------------------------

CREATE TABLE "SRITEST"."TAB1"
( "COL1" NUMBER,
"COL2" VARCHAR2(20),
PRIMARY KEY ("COL1") ENABLE
)


SQL> SELECT DBMS_METADATA.GET_DDL ('MATERIALIZED_VIEW','MV1','SRITEST') FROM DUAL;

DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV1','SRITEST')
--------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW "SRITEST"."MV1" ("COL1", "COL2")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS SELECT "TAB1"."COL1" "COL1","TAB1"."COL2" "COL2" FROM "TAB1" "TAB1"


SQL> CONN / AS SYSDBA;
Connected.
SQL> DROP USER SRITEST CASCADE;

User dropped.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms