LONG identifiers are one of the key features in Oracle Database 18.104.22.168. 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 22.214.171.124. 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 126.96.36.199 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
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.
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