My Oracle Support Banner

ORA-31693 ORA-14501 When Loading Data Into A Non-Partitioned Table With DataPump Import(IMPDP) (Doc ID 1331413.1)

Last updated on FEBRUARY 25, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

After exporting a partitioned table and trying to import the data into a non-partitioned table but with the same structure, fails with:

ORA-31693: Table data object "<SCHEMA_NAME>"."<TABLE_NAME>":"<PARTITION_NAME_2011P2>" failed to load/unload and is being skipped due to error:
ORA-14501: object is not partitioned

The import log file shows:

Import: Release 10.2.0.4.0 - Production on Sunday, 22 May, 2011 10:08:59

Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "<SCHEMA_NAME>"."<DP_JOB_NAME>" successfully loaded/unloaded
Starting "<SCHEMA_NAME>"."<DP_JOB_NAME>": <USER_NAME>/<PASSWORD>@<SID> dumpfile=<DUMPFILE_NAME>.1.%U.dmp directory=<DIRECTORY_NAME> logfile=<LOGFILE_NAME>.1.impdp.log TABLE_EXISTS_ACTION=TRUNCATE PARALLEL=3 REMAP_TABLESPACE=<PARTITION_NAME_2009P1>:<TABLESAPCE_NAME>,
<PARTITION_NAME_2009P2>:<TABLESAPCE_NAME>,<PARTITION_NAME_2010P1>:<TABLESAPCE_NAME>,
<PARTITION_NAME_2010P2>:<TABLESAPCE_NAME>,<PARTITION_NAME_2010P3>:<TABLESAPCE_NAME>,
<PARTITION_NAME_2010P4>:<TABLESAPCE_NAME>,<PARTITION_NAME_2010P5>:<TABLESAPCE_NAME>,
<PARTITION_NAME_2010P6>:<TABLESAPCE_NAME>,<PARTITION_NAME_2011P1>:<TABLESAPCE_NAME>,
<PARTITION_NAME_2011P2>:<TABLESAPCE_NAME>,<PARTITION_NAME_2011P3>:<TABLESAPCE_NAME>,
<PARTITION_NAME_MAX>:<TABLESAPCE_NAME>
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "<SCHEMA_NAME>"."<TABLE_NAME>" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PARTITION_NAME_2010P1>" 2.833 GB 21255502 rows
. . imported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PARTITION_NAME_2009P2>" 5.622 GB 42039421 rows
. . imported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PARTITION_NAME_2011P1>" 2.191 GB 17118827 rows
. . imported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PARTITION_NAME_2010P4>" 2.204 GB 16644136 rows
. . imported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PARTITION_NAME_2010P5>" 2.174 GB 16928009 rows
. . imported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PARTITION_NAME_2010P3>" 2.155 GB 16188003 rows
. . imported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PARTITION_NAME_2010P6>" 2.020 GB 15781061 rows
. . imported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PARTITION_NAME_2010P2>" 2.032 GB 15249170 rows
ORA-31693: Table data object "<SCHEMA_NAME>"."<TABLE_NAME>":"<PARTITION_NAME_2011P2>" failed to load/unload and is being skipped due to error:
ORA-14501: object is not partitioned
. . imported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PARTITION_NAME_2009P1>" 18.85 MB 141670 rows
. . imported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PARTITION_NAME_2011P3>" 0 KB 0 rows
. . imported "<SCHEMA_NAME>"."<TABLE_NAME>":"<PARTITION_NAME_MAX>" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Job "<SCHEMA_NAME>"."<DP_JOB_NAME>" completed with 2 error(s) at 10:15:59

Changes

 

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!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.