Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

dbms_metadata.get_ddl() gives table definition differently in 19c than 12c

Pralhad PatilNov 23 2020

Hi All,
Greetings !
I come across one issue in our chival utility of one of our enterprise product. We have used dbms_metadata package to get the table and constraint definitions . Using our dynamic code for archival of the table in offline mode (application is not running ) we create swap tables using CTAS , create primary keys and constraints . Our code is running well till 12c. however in 19c because of dbms_metadta,get_ddl returns constraint definition without storage and table_space clause my code logic failed.
I tried all EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM sort of options to get the same definition which i got in 12 however i can't . database Compatibility is 19.0. I tried optimizer_feature_enabled , also tried version parameter in get_ddl however i cant able to produce similar definition which i get in 12c.
in 12 c i got
ALTER TABLE "BLADELOGIC"."SW00010001" ADD CONSTRAINT "XPKSW00010001" PRIMARY KEY ("JOB_EVENT_ID") USING INDEX PCTFREE 10 INITRANS 6 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING TABLESPACE "BLADELOGIC_INDEX" ENABLE"
in 19c
ALTER TABLE "BLADELOGIC"."SW00010001" ADD CONSTRAINT "XPKSW00010001" PRIMARY KEY ("JOB_EVENT_ID") USING INDEX "BLADELOGIC"."XPKSW00010001" ENABLE
my logic expect oracle will create primary key index get create automatically.
So can anyone please help to understand this behaviour change in 12c amd 19c.

Thanks,
Pralhad.

Comments
Post Details
Added on Nov 23 2020
3 comments
4,431 views