DataPump Fails With Error ORA-39779 While Importing The Staging Table
(Doc ID 1420830.1)
Last updated on FEBRUARY 03, 2019
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.5 to 11.2.0.1 - Release: 10.2 to 11.2Information 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
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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms
Cause
Solution
References