CODEWITHSUNDEEP
oracle-databaseoracle12c
user1578653
user1578653

Reputation: 5028

Why is Oracle's dbms_metadata.get_ddl returning different results?

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?

Upvotes: 0

Views: 243

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17924

Look at DBMS_METADATA.SET_TRANSFORM_PARAM, particularly the STORAGE param.

It looks like your first call to DBMS_METADATA.GET_DDL had it enabled (which is the default setting), but the second call did not.

If you care about getting the exact same results each time, then take care to call SET_TRANSFORM_PARAM ahead of time for every setting that affects your output.

Upvotes: 3

Related Questions