This discussion is archived
7 Replies Latest reply: Feb 14, 2013 11:15 PM by vivalavida RSS

ORA-39779 during import of types

vivalavida Journeyer
Currently Being Moderated
Hi ,

I have executed an export with the following parameters:
DIRECTORY=DATAPUMP_DIR
DUMPFILE=exportdp_FULL_%U.dmp
LOGFILE=exportdp_FULL.log
FLASHBACK_SCN=6070655498233
FULL=yes
CONTENT=ALL
FILESIZE=2G
PARALLEL=2

The source database is of release 10.2.0.4.0 and installed on a HP-UX (HP-UX 11.23) environment.
The target database is of release 10.2.0.5.0 and installed on Red Hat Linux (Red Hat Enterprise Linux Server release 5.8 ) environment.

The parameterfile for the import has the following parameters:
DUMPFILE=exportdp_FULL_%U.dmp
LOGFILE=importdp_FULL.log
SCHEMAS=schema_a, schema_b, schema_c, schema_d
REMAP_TABLESPACE=DEFAULTUSER:DEFAULT_USER
PARALLEL=4
METRICS=yes

During the import the following errors get raised:
ORA-31693: Table data object "SCHEMA_A"."TEST" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-39779: type "SCHEMA_A"."OT_TEST" not found or conversion to latest version is not possible

Going through MOS and several forums none of the offered solutions (e.g. TRANSFORM=oid:n) solved this issue.

Currently I really don't have idea how to solve this issue - it's not clear to me what causes the problem. Running the query
SELECT o.oid$
, t.hashcode
, o.name
FROM sys.obj$ o
, sys.user$ u
, sys.type$ t
WHERE o.owner# = u.user#
AND o.oid$ = t.toid
AND o.name = 'OT_TEST';

gives different results on source- and target-database (oid and hashcode do both differ).

Any help will truly appreciated.

Rgds
Jan
  • 1. Re: ORA-39779 during import of types
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    If you manually create the type and then manually create the table do you get any error?

    Cheers,
    Harry
  • 2. Re: ORA-39779 during import of types
    vivalavida Journeyer
    Currently Being Moderated
    Hi ,
    might be an idea. But since I want to import a complete schema (with all objects) it will be alot of work to create all types in advance.
    But thanks anyway - I will try your idea if it will work or not...

    Rgds
    Jan
  • 3. Re: ORA-39779 during import of types
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post complete expdp and impdp commands used, along with the first 15 lines of the expdp and impdp logs.

    HTH
    Srini
  • 4. Re: ORA-39779 during import of types
    vivalavida Journeyer
    Currently Being Moderated
    Hi ,

    expdp command:
    expdp sys_admin parfile=exportdp_FULL.param;

    first 15 lines of exportdp_FULL.log:
    Export: Release 10.2.0.4.0 - 64bit Production on Mittwoch, 06 Februar, 2013 7:35:52

    Copyright (c) 2003, 2007, Oracle. All rights reserved.
    ;;;
    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Starting "SYS_ADMIN"."SYS_EXPORT_FULL_01": sys_admin/******** parfile=exportdp_FULL.param
    Estimate in progress using BLOCKS method...
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 199.2 GB
    Processing object type DATABASE_EXPORT/TABLESPACE
    Processing object type DATABASE_EXPORT/PROFILE
    Processing object type DATABASE_EXPORT/SYS_USER/USER
    Processing object type DATABASE_EXPORT/SCHEMA/USER
    Processing object type DATABASE_EXPORT/ROLE
    Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
    Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
    Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
    Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
    Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA

    impdp command:
    impdp sys_admin parfile=importdp_FULL.param;

    first 15 lines of importdp_FULL.log:
    Import: Release 10.2.0.5.0 - 64bit Production on Thursday, 07 February, 2013 12:53:58

    Copyright (c) 2003, 2007, Oracle. All rights reserved.
    ;;;
    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
    With the Partitioning, Real Application Clusters, OLAP, Data Mining
    and Real Application Testing options
    Master table "SYS_ADMIN"."SYS_IMPORT_SCHEMA_02" successfully loaded/unloaded
    Starting "SYS_ADMIN"."SYS_IMPORT_SCHEMA_02": sys_admin/******** parfile=importdp_FULL.param
    Processing object type DATABASE_EXPORT/SCHEMA/USER
    Failing sql is:
    CREATE USER "SCHEMA_A" IDENTIFIED BY VALUES 'A3E66F1F8E9EFE4E' DEFAULT TABLESPACE "DATA" TEMPORARY TABLESPACE "TEMP" PROFILE "SCHEMA_OWNER"
    ORA-31684: Object type USER:"SCHEMA_A" already exists
    ORA-31684: Object type USER:"SCHEMA_B" already exists
    ORA-39083: Object type USER failed to create with error:
    ORA-02380: profile TECHNICAL_USER does not exist

    The errors of the import are due to the fact that I have created all users before...

    Rgds
    Jan
  • 5. Re: ORA-39779 during import of types
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    Another thought. Is compatible set to the same version on both databases?

    Cheers,
    Harry
  • 6. Re: ORA-39779 during import of types
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    This looks good - I would suggest you open an SR with Support

    HTH
    Srini
  • 7. Re: ORA-39779 during import of types
    vivalavida Journeyer
    Currently Being Moderated
    Hi,
    No in source database it's set to 10.2.0.0.0 and in target database to 10.2.0.5.0.

    Rgds
    Jan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points