Friday, February 26, 2010

IMP-00017: following statement failed with ORACLE error 2304

Oracle import fails when importing into the same database but to a different schema, with this error. Problem is the type is exported with its object id (OID) in it's definition. When oracle tries to recreate it in a different schema it fails because OID is unique database wide. Here are the solutions.

On oracle 8i when using fromuser/touser, see the following
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1323002416801

On oracle 10.1 when using REMAP_SCHEMA
Create the types and tables dependent on those type and then use the option TABLE_EXISTS_ACTION=append on impdp

On oracle 10.2
when using REMAP_SCHEMA
use option
TRANSFORM=oid:n on impdp

here are the metalink notes:
Note: 351519.1 - Import of Object Types Fails With ORA-39083 ORA-02304 or ORA-39117 ORA-39779
Note: 1066139.6 "IMP-17 IMP-3 ORA-2304 IMP-63 FROMUSER/TOUSER Import of Table With Object Column"
Note :1066139.6 - IMP-17 IMP-3 ORA-2304 IMP-63 FROMUSER/TOUSER Import of Table With Object Column