DataPump Fails With Error ORA-39779 While Importing The Staging Table

(Doc ID 1420830.1)

Last updated on FEBRUARY 24, 2012

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.5 to 11.2.0.1 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms

 You try to move the staging table (STS) using the DataPump export/import and during the import execution the following error is raised:

ORA-31693: Table data object "TC"."STG_TC_DB_CPLANS" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-39779: type "SYS"."SQL_PLAN_STAT_ROW_TYPE" not found or conversion to latest version is not possible

The example below shows the issue:

-- source database (10.2.0.5) created with NLS_LENGTH_SEMANTICS=CHAR
sqlplus tc/tc
alter user tc default tablespace users;
create table test (n number );

-- populate the table
begin
  for i in 1 .. 10000 loop
    insert into test values (i);
  end loop;
  commit;
end;
/

create index test_idx on test (n);

analyze table test estimate statistics;

insert into test values (121212);
commit;

connect / as sysdba

-- run CREATE_SQLSET procedure to creates an empty STS
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET (sqlset_name => 'STS_TESTCASE_10205_11202',
    description => 'STS for testcase_10205_11202');
END;
/

-- load the data in STS_TESTCASE_10205_11202:
DECLARE
  cur DBMS_SQLTUNE.SQLSET_CURSOR;

BEGIN
  OPEN cur FOR SELECT VALUE(P) FROM table (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (167, 168, 'parsing_schema_name NOT IN (''DBSNMP'')', NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) P;
 
    DBMS_SQLTUNE.LOAD_SQLSET (sqlset_name => 'STS_TESTCASE_10205_11202', populate_cursor => cur, load_option => 'MERGE', update_option => 'ACCUMULATE');
END;
/

-- create the staging table
BEGIN
  DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (table_name => 'STG_TC_DB', schema_name => 'TC', tablespace_name => 'SYSAUX');
END;
/

-- export of the STS in the staging_table STG_TC_DB
BEGIN
  DBMS_SQLTUNE.PACK_STGTAB_SQLSET (sqlset_name => 'STS_TESTCASE_10205_11202', sqlset_owner => 'SYS', staging_table_name => 'STG_TC_DB', staging_schema_owner => 'TC');
END;
/

 Export the staging table with:

#> expdp tc/tc tables=TC.stg_tc_db directory=dptest dumpfile=spa_tc_db_102050_11202.dmp

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, OLAP, Data Mining and Real Application Testing options
Starting "TC"."SYS_EXPORT_TABLE_01": TC/******** tables=STG_TC_DB directory=dptest dumpfile=spa_tc_db_102050_11202.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 960 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "TC"."STG_TC_DB_CPLANS"      224.2 KB         559 rows
. . exported "TC"."STG_TC_DB"             110.9 KB          87 rows
. . exported "TC"."STG_TC_DB_CBINDS"      9.492 KB           0 rows
Master table "TC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TC.SYS_EXPORT_TABLE_01 is:
/tmp/spa_tc_db_102050_11202.dmp
Job "TC"."SYS_EXPORT_TABLE_01" successfully completed at 09:26:28

 Import the staging table in the target database fails with:

#> impdp tc/tc tables=tc.stg_tc_db directory=dptest dumpfile=spa_tc_db_102050_11202.dmp

Import: Release 11.2.0.2.0 - Production on Tue Oct 18 10:27:45 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TC"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TC"."SYS_IMPORT_TABLE_01": tc/******** tables=tc.stg_tc_db directory=dptest dumpfile=spa_tc_db_102050_11202.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "TC"."STG_TC_DB_CPLANS" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-39779: type "SYS"."SQL_PLAN_STAT_ROW_TYPE" not found or conversion to latest version is not possible
. . imported "TC"."STG_TC_DB"            110.9 KB     87 rows
. . imported "TC"."STG_TC_DB_CBINDS"     9.492 KB      0 rows
Job "TC"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 10:28:12

Note: The import runs successfully with the same test case, when source database is 11.2.0.2 created with NLS_LENGTH_SEMANTICS=CHAR and target database is 11.2.0.2.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms