Oracle Database 12cR2 Introduces 128 Byte Identifiers

Oracle Database 12cR2 Introduces 128 byte Identifiers.

Let’s take a look at a simple Table and Column name example:

If only this were available sooner … like 6 months ago (On-Prem of course). In 12cR2, the new Identifier length limit is now 128 bytes. The 30 character limit on object names made it a pain for migrations from other databases without this limitation. Finally we are now able to some direct mappings when migrating from databases such as IBM’s DB2 to Oracle for example.

Try this in 12cR1:

create table long_name_because_this_is_a_new_feature_in_12cR2_and_it_is_great ( super_long_name_for_table_columns_work_as_well_in_the_latest_release VARCHAR2(20) );

Now try it again in 12cR2:

create table long_name_because_this_is_a_new_feature_in_12cR2_and_it_is_great ( super_long_name_for_table_columns_work_as_well_in_the_latest_release VARCHAR2(20) );

Now I wouldn’t recommend going crazy and start using ridiculously long names as you will still need to consider backward compatibility, but if you are migrating from another database engine, this can be quite beneficial.

  • Don’t need to truncate names at the 30 character limit
  • Reduces risk of duplicate identifiers due to truncate
  • More descriptive names (30 characters is a decent, but more is better … right?)
  • Space is cheap so who cares about using sentences to name your tables
  • I’m sure there are more.

Enjoy


Originally published at My Experiences with Oracle Technology.