Skip to Main Content
  • Questions

  • Data pump import unable to import table that data pump export exported

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Thomas.

Asked: November 05, 2018 - 4:01 pm UTC

Last updated: November 07, 2018 - 1:13 am UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

I'm having trouble importing a table that export exported. The error I'm getting is

"ORA-14308: partition bound element must be one of: string, datetime or interval literal, number, or NULL",

but according to the documentation

"The partitioning key columns must be of type CHAR, NCHAR, VARCHAR2, NVARCHAR2, VARCHAR, NUMBER, FLOAT, DATE, TIMESTAMP, TIMESTAMP WITH LOCAL TIMEZONE, or RAW. "

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-TABLE.html

As a workaround, if I pre-create the table with the original sql and run the import with TABLE_EXISTS_ACTION=truncate it works.

Here is my example:

$ sqlplus tom/***

SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 5 10:06:11 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Mon Nov 05 2018 10:01:03 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set echo on
SQL> @test1.sql
SQL> create table test
  2  (
  3     id         raw(16),
  4     groupid    raw(16),
  5     chardata   varchar2(20),
  6     numbdata   number,
  7     datedata   date
  8  )
  9    partition by list (groupid) automatic (partition P_ values (null))
 10  ;

Table created.

SQL>
SQL> alter table test
  2  add constraint test_pk
  3  primary key (id)
  4  using index;

Table altered.

SQL>
SQL>
SQL> insert into test
  2  values (sys_guid(), sys_guid(), 'some char data', 123, sysdate - 3);

1 row created.

SQL>
SQL> insert into test
  2  values (sys_guid(), sys_guid(), 'other char data', 234, sysdate - 7);

1 row created.

SQL>
SQL> insert into test
  2  values (sys_guid(), sys_guid(), 'more char data', 345, sysdate - 2);

1 row created.

SQL>
SQL>
SQL> commit;

Commit complete.

SQL>
SQL>
SQL>
SQL> select * from test;

ID                               GROUPID
-------------------------------- --------------------------------
CHARDATA                                                       NUMBDATA
------------------------------------------------------------ ----------
DATEDATA
---------------
26B315C36F484F7680A34413FF620339 EAD0CC4371614784B12FC1F6E18A3749
some char data                                                      123
02-NOV-18

826380FA5A5844898A0B86ED9ACA65C5 2F5CC482C696447FAB12B41FEEE2A69E
other char data                                                     234
29-OCT-18

AB689A125E8241D39E9965523580EA85 77B6950A67D84A7F9F23749981B96148
more char data                                                      345
03-NOV-18


SQL>
SQL>
SQL> select table_name, partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'TEST';

TABLE_NAME
--------------------------------------------------------------------------------
PARTITION_NAME
--------------------------------------------------------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------
TEST
P_
null

TEST
SYS_P29718
HEXTORAW('EAD0CC4371614784B12FC1F6E18A3749')

TEST
SYS_P29719
HEXTORAW('2F5CC482C696447FAB12B41FEEE2A69E')

TEST
SYS_P29720
HEXTORAW('77B6950A67D84A7F9F23749981B96148')


SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


$ expdp tom/*** directory=DATA_PUMP_DIR dumpfile=test_exp.dmp tables=test

Export: Release 12.2.0.1.0 - Production on Mon Nov 5 10:20:51 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "TOM"."SYS_EXPORT_TABLE_01":  tom/******** directory=DATA_PUMP_DIR dumpfile=test_exp.dmp tables=test
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/AUDIT_OBJ
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TOM"."TEST":"P_"                               0 KB       0 rows
. . exported "TOM"."TEST":"SYS_P29718"                   6.804 KB       1 rows
. . exported "TOM"."TEST":"SYS_P29719"                   6.804 KB       1 rows
. . exported "TOM"."TEST":"SYS_P29720"                   6.804 KB       1 rows
Master table "TOM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TOM.SYS_EXPORT_TABLE_01 is:
  C:\APP\SVCORACLE\ADMIN\JACOBDB\DPDUMP\65B63FD06D4949AC86AE4C28C7C29310\TEST_EXP.DMP
Job "TOM"."SYS_EXPORT_TABLE_01" successfully completed at Mon Nov 5 10:29:47 2018 elapsed 0 00:08:53


$ sqlplus tom/***

SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 5 10:30:19 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Mon Nov 05 2018 10:20:51 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> drop table test purge;

Table dropped.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


$ impdp tom/*** directory=DATA_PUMP_DIR dumpfile=test_exp.dmp tables=test

Import: Release 12.2.0.1.0 - Production on Mon Nov 5 10:31:03 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "TOM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TOM"."SYS_IMPORT_TABLE_01":  tom/******** directory=DATA_PUMP_DIR dumpfile=test_exp.dmp tables=test
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"TOM"."TEST" failed to create with error:
ORA-14308: partition bound element must be one of: string, datetime or interval literal, number, or NULL

Failing sql is:
CREATE TABLE "TOM"."TEST" ("ID" RAW(16), "GROUPID" RAW(16), "CHARDATA" VARCHAR2(20 BYTE), "NUMBDATA" NUMBER, "DATEDATA" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"  PARTITION BY LIST ("GROUPID") AUTOMATIC  (PARTITION "P_"  VALUES (null) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"  READ WRITE , PARTITION "SYS_P29718"  VALUES (HEXTORAW('EAD0CC4371614784B12FC1F6E18A3749')) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"  READ WRITE , PARTITION "SYS_P29719"  VALUES (HEXTORAW('2F5CC482C696447FAB12B41FEEE2A69E')) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"  READ WRITE , PARTITION "SYS_P29720"  VALUES (HEXTORAW('77B6950A67D84A7F9F23749981B96148')) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 8

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/AUDIT_OBJ
ORA-39112: Dependent object type AUDIT_OBJ skipped, base object type TABLE:"TOM"."TEST" creation failed

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"TOM"."TEST_PK" skipped, base object type TABLE:"TOM"."TEST" creation failed

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TOM"."SYS_IMPORT_TABLE_01" completed with 3 error(s) at Mon Nov 5 10:34:17 2018 elapsed 0 00:03:11

and Connor said...

Yeah I reckon that's a bug. Putting aside data pump, there is still some inconsistency in what we're allowing and what we are not, eg

SQL> create table test
  2  (
  3     id         raw(16),
  4     groupid    raw(16),
  5     chardata   varchar2(20),
  6     numbdata   number,
  7     datedata   date
  8  )  partition by list (groupid) (partition P_ values ('FF'));

Table created.

SQL> create table test
  2  (
  3     id         raw(16),
  4     groupid    raw(16),
  5     chardata   varchar2(20),
  6     numbdata   number,
  7     datedata   date
  8  )  partition by list (groupid) (partition P_ values (hextoraw('FF')));
)  partition by list (groupid) (partition P_ values (hextoraw('FF')))
                                                                   *
ERROR at line 8:
ORA-14308: partition bound element must be one of: string, datetime or interval literal, number, or NULL


I suggest you log a bug with Support (bugs carry more weight if they come from customers than if we do it)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Help!

Thomas Brotherton, November 06, 2018 - 9:38 pm UTC

I did report it as a bug but am being told that it isn't a bug and that I shouldn't be using the syntax that I am not generating. Can you help chime in on SR 3-18695160501?

Thanks.
Connor McDonald
November 07, 2018 - 1:13 am UTC

I have no access to SR's (we do that for security reasons).

But I'd add to the SR - any table that you create *must* be able to be exported and imported with DataPump.

So (at the very least) it is a datapump bug.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database