Oracle 12c – Why dbms_metadata.get_ddl Returns Different Results

oracleoracle-12c

I'm trying to compare two schemas using dbms_metadata.get_ddl. The two schemas were created using exactly the same SQL scripts. However on one primary key I'm getting a difference in the DDLs:

ALTER TABLE "SCHEMA_NAME"."AUDIT_EVENTS_LOG" ADD CONSTRAINT "AUDIT_EVENTS_LOG_PK" PRIMARY KEY ("LOG_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  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)
  TABLESPACE "DATA_INDEX"  ENABLE

Vs:

  ALTER TABLE "SCHEMA_NAME"."AUDIT_EVENTS_LOG" ADD CONSTRAINT "AUDIT_EVENTS_LOG_PK" PRIMARY KEY ("LOG_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE "DATA_INDEX"  ENABLE

Both schemas are located on the same database instance. The script for creating this primary key, used for both schemas, is:

ALTER TABLE "AUDIT_EVENTS_LOG" ADD CONSTRAINT "AUDIT_EVENTS_LOG_PK" PRIMARY KEY ("LOG_ID")
  USING INDEX  TABLESPACE "DATA_INDEX" ENABLE;

What could cause the difference in result for GET_DDL, when both are created using exactly the same script?

Best Answer

You can control whether or not the storage parameter is included by using DBMS_METADATA.SET_TRANSFORM_PARAM. It's possible that you changed this in one of your sessions.

To suppress the storage parameter, execute the following and then run the GET_DDL again:

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);

When you're finished, you can set it back to the default by running this:

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');
Related Question