Export with Data Pump and Long Identifiers

I blogged a few days ago about Long Identifiers in Oracle Database 12.2 and accessing the objects via database links from lower database version. As this raised a few questions, I realized there may be a bit more clarification necessary. One question was about what happens during export with Data Pump and Long Identifiers. That’s a pretty good question.

Export with Data Pump and Long Identifiers

Export with Data Pump and Long Identifiers

I’d like to demonstrate the effect with a short example. I’m doing all my tests in a fresh PDB inside an Oracle 18.1.0 CDB from out Hands-On Lab. But you can repeat it with every 12.2 or 18c database of course.

First I’m creating a table using a long identifier (>30 byte name and column name length):

create pluggable database PDB2
  admin user adm identified by adm
  file_name_convert=('/oradata/CDB2/pdbseed', '/oradata/CDB2/pdb2');
alter session set container=PDB2;
startup;
create table system.T123456789012345678901234567890 (C123456789012345678901234567890 number);
insert into system.T123456789012345678901234567890 values (1);
insert into system.T123456789012345678901234567890 values (2);
commit;

Afterwards I will need to setup a few pieces for Data Pump:

create directory mydir as '/u02/oradata/CDB2/mydir';
grant read, write on directory mydir to system;
exit

Export without VERSION

In the first run I will export this particular table without using the VERSION parameter. With VERSION you advice Data Pump to create a file of a format with content understandable by the version you specify with VERSION.

$ expdp system/oracle@pdb2 tables=T123456789012345678901234567890 exclude=statistics directory=mydir logfile=pdb2.log dumpfile=noversion.dmp logtime=all

Export: Release 18.0.0.0.0 - Production on Mon Jul 9 17:50:56 2018
Version 18.1.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
09-JUL-18 17:51:00.147: Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@pdb2 tables=T123456789012345678901234567890 exclude=statistics directory=mydir logfile=pdb2.log dumpfile=noversion.dmp logtime=all 
09-JUL-18 17:51:03.537: Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
09-JUL-18 17:51:06.631: Processing object type TABLE_EXPORT/TABLE/TABLE
09-JUL-18 17:51:09.452: . . exported "SYSTEM"."T123456789012345678901234567890"  5.117 KB       2 rows
09-JUL-18 17:51:11.073: Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
09-JUL-18 17:51:11.105: ******************************************************************************
09-JUL-18 17:51:11.109: Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
09-JUL-18 17:51:11.116:   /u02/oradata/CDB2/mydir/noversion.dmp
09-JUL-18 17:51:11.147: Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jul 9 17:51:11 2018 elapsed 0 00:00:13

This looks very good. No issues at all.

Export with VERSION

But as soon as I advice Data Pump to export in a format which is supported for a 12.1 import, I receive at first a warning, then afterwards error. In this example I do include a second table (without long identifiers) to demonstrate that the “regular” table object still gets exported:

$ expdp system/oracle@pdb2 tables=T123456789012345678901234567890,TAB1 exclude=statistics directory=mydir logfile=pdb2.log dumpfile=withversion.dmp logtime=all VERSION=12.1

Export: Release 18.0.0.0.0 - Production on Mon Jul 9 18:00:29 2018
Version 18.1.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Warning: Oracle Data Pump is exporting from a database that supports long identifiers to a version that does not support long identifiers.

09-JUL-18 18:00:32.933: Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@pdb2 tables=T123456789012345678901234567890,TAB1 exclude=statistics directory=mydir logfile=pdb2.log dumpfile=withversion.dmp logtime=all VERSION=12.1 
09-JUL-18 18:00:36.555: Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
09-JUL-18 18:00:36.590: ORA-39373: cannot export TABLE_DATA:"SYSTEM"."T123456789012345678901234567890" to version 12.1 due to long identifiers

09-JUL-18 18:00:39.354: Processing object type TABLE_EXPORT/TABLE/TABLE
09-JUL-18 18:00:39.369: ORA-39373: cannot export TABLE:"SYSTEM"."T123456789012345678901234567890" to version 12.1 due to long identifiers

09-JUL-18 18:00:42.065: . . exported "SYSTEM"."TAB1"                             5.062 KB       2 rows
09-JUL-18 18:00:42.193: ORA-39166: Object SYSTEM.T123456789012345678901234567890 was not found or could not be exported or imported.
09-JUL-18 18:00:43.598: Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
09-JUL-18 18:00:43.625: ******************************************************************************
09-JUL-18 18:00:43.629: Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
09-JUL-18 18:00:43.632:   /u02/oradata/CDB2/mydir/withversion.dmp
09-JUL-18 18:00:43.661: Job "SYSTEM"."SYS_EXPORT_TABLE_01" completed with 3 error(s) at Mon Jul 9 18:00:43 2018 elapsed 0 00:00:13

It does not lead to a fatal error but the table and dependent objects aren’t in the dump file.

Take note of the warning in the log:

Warning: Oracle Data Pump is exporting from a database that supports long identifiers to a version that does not support long identifiers.

Video

See also a short 5 min video demonstration the differences:

.

Further Information

–Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.