LONG identifiers are one of the key features in Oracle Database 12.2.0.1. But as I learned the other day, you should be a bit careful when you exchange data between different databases. In certain situations, Long Identifiers in Oracle 12.2 may cause trouble.
What are Long Identifiers?
Long identifiers get introduced with Oracle Database 12.2.0.1. For ages, you could name a table or a column or a user or a package in Oracle with only 30 byte length. Hence, naming a table TAB_THIS_IS_MY_TABLE_AND_I_LIK is ok (30 byte – 30 characters) but naming it TAB_THIS_IS_MY_TABLE_AND_I_LIKE_IT is not possible as it requires 33 byte. And please NEVER EVER name a table with special characters such as German umlauts (Ä,Ö,Ü) or the sharp s (ß). Don’t!
With Oracle Database 12.2.0.1 this limitation gets lifted. For most identifiers in Oracle you can use now up to 128 byte. Or characters if you refrain from using special characters. You can easily name your table now TAB_THIS_IS_MY_TABLE_AND_I_LIKE_IT – or even longer such as TAB_THIS_IS_MY_TABLE_AND_I_LIKE_IT_AS_I_LIKE_LONG_TABLE_NAMES.
As soon as you set COMPATIBLE to “12.2.0” or higher, you can use the Long identifiers for every object in Oracle with the following exceptions:
- Database name ≤ 8 byte
- Disk groups ≤ 30 byte
- PDB names ≤ 30 byte
- Rollback segments ≤ 30 byte
- Tablespace names ≤ 30 byte
- Tablespace sets ≤ 30 byte
There’s no initialization parameter enabling or disabling Long Identifiers.
Long Identifiers in Oracle 12.2 may cause trouble
But the other day I came across an interesting case. When you have a database with COMPATIBLE ≥ “12.2.0” where you created for instance a table with a name longer than 30 byte, but you’d like to access this table or its data via a database link from another database which has a lower COMPATIBLE setting (or generally a lower release), you will see:
SQL> select * from hugo.TAB_THIS_IS_MY_TABLE_AND_I_LIKE_NAMES_LONGER_THAN_30_BYTE@my_dblink;
select * from hugo.TAB_THIS_IS_MY_TABLE_AND_I_LIKE_NAMES_LONGER_THAN_30_BYTE@my_dblink;
ORA-00972: identifier is too long
This is expected and no bug of course. But you need to be aware of it.
Furthermore, the same problem can arise with Materialized Views Logs. Be aware that an MV Log automatically attaches a prefix of MLOG$_ to the table name. This is ok in a database with COMPATIBLE ≥ “12.2.0” but may cause issues when you query them remotely.
More Information
You’ll find more information in the Oracle Documentation:
- Oracle 12.2 SQL Language Reference – 2.8.1 Database Object Naming Rules
- Export with Data Pump and Long Identifiers
–Mike
Thanks for the post
Mike,
Will this cause issues with any data movement utility like export/import, TTS, GoldenGate etc. where I am moving data from 12.2 to say 12.1 or 11.2?
Thanks,
Arun
Arun,
when you move data from 12.2 to 12.1 you’ll have to use VERSION=12.1 with data pump as otherwise you’ll be in trouble most likely. This would ignore Long Indentifier names. TTS does not work to a lower version – so no risk in this area. And I’d guess OGG would run into the same issues as Data Pump in case you use Long Identifiers.
Cheers,
Mike
Mike,
A quick clarification. When you say ignore, does it mean the long identifier names will be truncated to 30 bytes or will those objects be skipped altogether during import?
Thanks,
Arun
Arun,
it will raise an error.
See here – /a/ without VERSION parameter – all fine.
$ expdp system/oracle@pdb2 tables=T123456789012345678901234567890 exclude=statistics directory=mydir logfile=pdb2.log
Export: Release 18.0.0.0.0 – Production on Mon Jul 9 17:32:44 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
Starting “SYSTEM”.”SYS_EXPORT_TABLE_01″: system/********@pdb2 tables=T123456789012345678901234567890 exclude=statistics directory=mydir logfile=pdb2.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “SYSTEM”.”T123456789012345678901234567890″ 5.117 KB 2 rows
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u02/oradata/CDB2/mydir/expdat.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully completed at Mon Jul 9 17:33:02 2018 elapsed 0 00:00:14
and now /b/ with VERSION:
$ expdp system/oracle@pdb2 tables=T123456789012345678901234567890 exclude=statistics directory=mydir logfile=pdb2.log VERSION=12.1 dumpfile=vers.dmp
Export: Release 18.0.0.0.0 – Production on Mon Jul 9 17:34:32 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.
Starting “SYSTEM”.”SYS_EXPORT_TABLE_01″: system/********@pdb2 tables=T123456789012345678901234567890 exclude=statistics directory=mydir logfile=pdb2.log VERSION=12.1 dumpfile=vers.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-39373: cannot export TABLE_DATA:”SYSTEM”.”T123456789012345678901234567890″ to version 12.1 due to long identifiers
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39373: cannot export TABLE:”SYSTEM”.”T123456789012345678901234567890″ to version 12.1 due to long identifiers
ORA-39166: Object SYSTEM.T123456789012345678901234567890 was not found or could not be exported or imported.
ORA-31655: no data or metadata objects selected for job
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ completed with 4 error(s) at Mon Jul 9 17:34:44 2018 elapsed 0 00:00:11
Cheers,
Mike
Arun,
see here:
https://mikedietrichde.com/2018/07/09/export-with-data-pump-and-long-identifiers/
Cheers,
Mike
THANK YOU !!! This information was valuable!
Hi Mike,
Running terraform against Oracle 12.1.0.2.200414 in OCI with pdb_name longer than 8 characters gives this error:
Error: Service error:InvalidParameter. The pdb name cannot be longer than 8 characters..
Is this restriction lifted in 12.2 and newer versions?
Kind regards,
Kjell Tore
Hi Kjell,
this is not a restriction but a bug in the OCI interface (I guess). You need to check with Oracle support on this – even before LONG identifiers you could have PDBs with longer names. It was 30 byte since the beginning.
Cheers,
Mike
Hello Mike,
Am trying to take export from DB version 12.2 for importing in Target db version 12.1 using below script,
expdp username/password directory=EXP_DIR01 dumpfile=ADEPTNEW_01_expdp.dmp schemas=ADEPTNEW VERSION=12.1 logfile=ADEPTNEW_expdp_1.log
But while taking export, getting below ORA-39373 error, Could you please help with solution for this to fix.
;;;
Export: Release 12.2.0.1.0 – Production on Tue Dec 14 18:40:15 2021
Copyright (c) 1982, 2020, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “ADEPTNEW”.”SYS_EXPORT_SCHEMA_01″: ADEPTNEW/******** directory=EXP_DIR01 dumpfile=ADEPTNEW_01_expdp.dmp schemas=ADEPTNEW VERSION=12.1 logfile=ADEPTNEW_expdp_1.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
ORA-39373: cannot export SYSTEM_GRANT:”ADEPTNEW” to version 12.1 due to long identifiers
ORA-39373: cannot export SYSTEM_GRANT:”ADEPTNEW” to version 12.1 due to long identifiers
ORA-39373: cannot export SYSTEM_GRANT:”ADEPTNEW” to version 12.1 due to long identifiers
ORA-39373: cannot export SYSTEM_GRANT:”ADEPTNEW” to version 12.1 due to long identifiers
ORA-39373: cannot export SYSTEM_GRANT:”ADEPTNEW” to version 12.1 due to long identifiers
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM.
Thanks in Advance
Hi Sundar,
actually there is not much I could do.
Can you please check the GRANTs manually and rebuild them potentially with shorter identifier names in the older release?
Thanks,
Mike