2

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?

  • Do both schemas use the same tablespace? The difference is only in the storage clause. – andba77 Sep 3 '15 at 17:01
3

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');
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.