How to use DBMS_METADATA to recreate indexes and PKs without ORA-955?

From: Rich J <rjoralist3_at_society.servebeer.com>
Date: Tue, 19 Feb 2019 08:56:40 -0600
Message-ID: <7fb4bb0bb1f5e910fc4a9f4b83e5ecaf_at_society.servebeer.com>



Hey all,

In 12.1.0.2, I have a package that uses DBMS_METADATA to extract, then recreate indexes and PKs of tables in order to do data refreshes. Originally created in 11.2, it does this for all indexes of a given table, then again for its constraints, which are only PKs:

        v_xform_handle :=
DBMS_METADATA.ADD_TRANSFORM(v_md_handle,'DDL');

        DBMS_METADATA.SET_TRANSFORM_PARAM(v_xform_handle, 'PRETTY', FALSE);
        DBMS_METADATA.SET_TRANSFORM_PARAM(v_xform_handle, 'SQLTERMINATOR', FALSE);
        DBMS_METADATA.SET_TRANSFORM_PARAM(v_xform_handle, 'STORAGE', FALSE); In 11.2, for a table with only a single unique index that supports the PK, it would generate something like this:

CREATE UNIQUE INDEX "MYSCHEMA"."MYTAB_0" ON "MYSCHEMA"."MYTAB" ("MYCOL") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE
"MYSCHEMAI"

ALTER TABLE "MYSCHEMA"."MYTAB" ADD CONSTRAINT "MYTAB_PK" PRIMARY KEY ("MYCOL") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE "MYSCHEMAI" ENABLE In 12.1.0.2, it generates:

CREATE UNIQUE INDEX "MYSCHEMA"."MYTAB_0" ON "MYSCHEMA"."MYTAB" ("MYCOL") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE
"MYSCHEMAI"

ALTER TABLE "MYSCHEMA"."MYTAB" ADD CONSTRAINT "MYTAB_PK" PRIMARY KEY ("MYCOL") USING INDEX (CREATE UNIQUE INDEX "MYSCHEMA"."MYTAB_0" ON
"MYSCHEMA"."MYTAB" ("MYCOL") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE
STATISTICS TABLESPACE "MYSCHEMAI" ) ENABLE The highlighted CREATE will cause the constraint creation to fail with ORA-955 because that index already exists. So now I have no PKs.

I can't find any documentation on this behavior change, or how one is supposed to filter out unique indexes that support PKs. I can change the package to create the PK first, which would cause the corresponding unique index to then fail, but that's a hack more than a fix.

Yes, 12.1 is now old. I'm upgrading later this year...

Thanks!
Rich

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 19 2019 - 15:56:40 CET

Original text of this message