Datapump bug using REF and user defined types

From: Luis <lcarapinha@xxxxxxxxx>
To: list <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 19 Feb 2013 15:54:27 +0000
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@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@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

--
//www.freelists.org/webpage/oracle-l
Datapump bug using REF and user defined types

Other related posts:
