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.

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

Share this: