Index metadata bug and ORA-22808

Yesterday a co-worker complained that the scripts from import/export using Datapump were not working due introduction of user defined Object Types. These object types are particulary useful to model real-world entities as objects in the database, it just happen that datapump scripts are throwing a lot of ORA-22808 when creating FK:

ORA-39083: Object type INDEX failed to create with error:
ORA-22808: REF dereferencing not allowed

I’ve found no really good articles on this issue (even on MOS), so i’ve decided to write one. First let’s create what we need to reproduce the issue, 2 types and two different tables.
The first type is the basic one and the second type contains a column called FooRef that is type REF wich means that target object must have an object identifier.
The first table (T1_MY_TABLE) is an ordinary heap table with a PRIMARY KEY on column Id that is created. The second table is a more complex since it contains a CONSTRAINT that referes first table. After this an INDEX is create on second table using FooRef.

CREATE TYPE MY_FOO_TYPE AS OBJECT
(
 Id INTEGER, 
 State INTEGER, 
 Name varchar2(255)
) NOT FINAL

CREATE TYPE MY_FOO_TYPE_2 AS OBJECT
(
 FooId INTEGER,
 FooStateId INTEGER,
 FooNativeName VARCHAR2(255),
 FooRef REF MY_FOO_TYPE
) NOT FINAL

CREATE TABLE T1_MY_TABLE OF MY_FOO_TYPE
(
 CONSTRAINT CK_Id_PK PRIMARY KEY (Id) USING INDEX
 (
 CREATE UNIQUE INDEX IDX_T1_MY_TABLE ON T1_MY_TABLE(Id) INITRANS 10 TABLESPACE GEN_IDX
 )
) OBJECT IDENTIFIER IS PRIMARY KEY TABLESPACE GEN_DATA;

CREATE TABLE T2_MY_TABLE OF MY_FOO_TYPE_2
(
 CONSTRAINT FooState_ID UNIQUE (FooStateId) USING INDEX
 (
 CREATE UNIQUE INDEX IDX_My_FOO_TYPE_2 ON T2_MY_TABLE(FooStateId) INITRANS 10 TABLESPACE GEN_IDX
 ),
 CONSTRAINT CK_R0 FooRef REFERENCES T1_MY_TABLE ON DELETE CASCADE,
 CONSTRAINT CK_R0 CHECK(FooRef IS NOT NULL),
 CONSTRAINT FooId_PK PRIMARY KEY (FooId) USING INDEX
 (
 CREATE UNIQUE INDEX IDX_FooId_PK ON T2_MY_TABLE(FooId) INITRANS 10 TABLESPACE GEN_IDX
 )
) OBJECT IDENTIFIER IS PRIMARY KEY TABLESPACE GEN_DATA;
CREATE INDEX IDX_FooId_FK ON T2_MY_TABLE(FooRef) INITRANS 10 TABLESPACE GEN_IDX;

Everything here runs without any issue if you try it. Now let’s try export and import using Datapump:

-bash-3.2$ expdp pmdb_luis/PMDB_LUIS@DB schemas=PMDB_LUIS 
directory=DATA_PUMP_DIR 
dumpfile=luis.dmp logfile=luis.log
...
Dump file set for PMDB_LUIS.SYS_EXPORT_SCHEMA_01 is:
 /opt/oracle/app/oracle/admin/TNMSAM/dpdump/luis.dmp
Job "PMDB_LUIS"."SYS_EXPORT_SCHEMA_01" successfully completed at 05:48:04

Now, let’s delete the objects and try the import.

DROP TABLE T2_MY_TABLE;
DROP TABLE T1_MY_TABLE;
DROP TYPE MY_FOO_TYPE_2;
DROP TYPE MY_FOO_TYPE;

-bash-3.2$ impdp pmdb_luis/PMDB_LUIS@TNMSAM schemas=PMDB_LUIS 
directory=DATA_PUMP_DIR dumpfile=luis.dmp logfile=luis.log
...
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "PMDB_LUIS"."T1_MY_TABLE" 0 KB 0 rows
. . imported "PMDB_LUIS"."T2_MY_TABLE" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-22808: REF dereferencing not allowed
Failing sql is:
CREATE INDEX "PMDB_LUIS"."IDX_FOOID_FK" ON "PMDB_LUIS"."T2_MY_TABLE" ("FOOREF"."ID") PCTFREE 10 INITRANS 10 MAXTRANS 255 STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "GEN_IDX" PARALLEL 1 
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"PMDB_LUIS"."IDX_FOOID_FK" creation failed
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "PMDB_LUIS"."SYS_IMPORT_SCHEMA_01" completed with 3 error(s) at 05:50:06

So we are stuck now. DDL created without any issues, export without any issue and import with problems creating the last index on second table. I little more investigation is needed to figure out what’s “the problem”. First let’s take out of equation datapump and investigate index drop and creation. Drop all objects and recreate again the DDL presented.

SQL> select index_name from user_indexes where INDEX_NAME = 'IDX_FOOID_FK';
INDEX_NAME
------------------------------
IDX_FOOID_FK

SQL> set long 100000
SQL> select dbms_metadata.get_ddl('INDEX','IDX_FOOID_FK','PMDB_LUIS') from dual;
DBMS_METADATA.GET_DDL('INDEX','IDX_FOOID_FK','PMDB_LUIS')
--------------------------------------------------------------------------------
CREATE INDEX "PMDB_LUIS"."IDX_FOOID_FK" ON "PMDB_LUIS"."T2_MY_TABLE" ("FOOREF"
."ID")
 PCTFREE 10 INITRANS 10 MAXTRANS 255 COMPUTE STATISTICS
 STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "GEN_IDX"

SQL> drop index IDX_FOOID_FK;
Index dropped.

SQL> CREATE INDEX "PMDB_LUIS"."IDX_FOOID_FK" ON "PMDB_LUIS"."T2_MY_TABLE" ("FOOREF"
 2 ."ID")
 3 PCTFREE 10 INITRANS 10 MAXTRANS 255 COMPUTE STATISTICS
 4 STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 5 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 6 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 7 TABLESPACE "GEN_IDX"; 
CREATE INDEX "PMDB_LUIS"."IDX_FOOID_FK" ON "PMDB_LUIS"."T2_MY_TABLE" ("FOOREF"
 *
ERROR at line 1:
ORA-22808: REF dereferencing not allowed

As you can see, index metadata stored in data dictionary is _wrong_ and is refering FOOREF.ID instead of FOOREF only. That _might_ be related with table created with OBJECT IDENTIFIER IS PRIMARY KEY.

It is not a Datapump issue itself, Datapump uses the metadata (DDL) data dictionary during the import (impdp) and since it is wrong it can’t create the index. Of course if you manually create the index using FOOREF instead of _wrong_ FOOREF.ID you will not find any issues.
I’ve contacted MOS and they opened a bug for this issue: Bug 16369430 : IMPDP REPORTS ORA-39083/ORA-22808 WHEN CREATING AN INDEX
You will also find there this exact test case 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s