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
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
