Datapump bug using REF and user defined types

From: Luis <lcarapinha_at_gmail.com>
Date: Tue, 19 Feb 2013 15:54:27 +0000
Message-ID: <CANx=q_YMw_RddNOPGhTGD+wniYEXogJWELQcv+P1BcZ_Hb8GBA_at_mail.gmail.com>

Hi list,
Env: 11.2.0.3.0, Solaris 10 SPARC

I have an issue here envolving REF and user defined types. I setup the following scenario:

1. 2 different types, MY_FOO_TYPE_2.FooRef is a reference to the first created type

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 2) Two different tables. One is just an heap table using the first created type and a INDEX and PK on column Id.

The second table have one constraint that is a reference to the first table. Please note that constraint is refering FooRef that is a column in TYPE MY_FOO_TYPE_2 that refers to previous type. In the end a INDEX on second table using FooRef column is created.

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; All this runs fine and everything is created without issues. Then a run datapump to export the schema:

expdp pmdb_luis/PMDB_LUIS_at_DB schemas=PMDB_LUIS directoryÚTA_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

Then i delete all 2 tables and 2 objects with the following SQL:

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

And finally i run the impdp:

impdp pmdb_luis/PMDB_LUIS_at_DB schemas=PMDB_LUIS directoryÚTA_PUMP_DIR dumpfile=luis.dmp logfile=luis.log

rocessing 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

I got here a ORA-22808 that for me doesn't make sense because after this error i can manually create the index that failed manually (IDX_FOOID_FK). Is this a datapump issue or i'm missing something?

Thank you very much,
Luís Marques
http://lcmarques.com

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 19 2013 - 16:54:27 CET