How to use DBMS_METADATA to recreate indexes and PKs without ORA-955?
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-lReceived on Tue Feb 19 2019 - 15:56:40 CET