Long Identifiers in Oracle 12.2 may cause trouble

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:

–Mike

6 thoughts on “Long Identifiers in Oracle 12.2 may cause trouble

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

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

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.