Longer Identifiers in Oracle Database 12c Release 2

Now that Oracle Database 12c Release 2 is available on-prem, I thought it would be a good time to remind folks about what they can expect from the new release. I’m going to divide topics into two categories, Marquee Features and top-tips. Let’s start with a top-tip!

Prior to Oracle Database 12c Release 2, all object names had been limited to just 30 bytes. This limitation lead to some interesting problems, especially if you wanted to use descriptive names for the database objects you were creating.

Take for example the dictionary tables we wanted to create in 12.1 to help manage SQL Plan Directives. The first table was DBA_SQL_PLAN_DIRECTIVES, with 23 characters, which wasn’t problem. However, the second table we wanted was DBA_SQL_PLAN_DIRECTIVES_OBJECTS.

SELECT product, version
FROM   product_component_version
WHERE  product LIKE 'Oracle%';
 
PRODUCT                                                VERSION
---------------------------------------------------    ---------------
 
Oracle DATABASE 12c Enterprise Edition                  12.1.0.2.0
 
CREATE TABLE dba_sql_plan_directives_objects
(
c1 NUMBER
);
 
CREATE TABLE dba_sql_plan_directives_objects(c1 NUMBER)
*
ERROR at line 1:
ORA-00972: identifier IS too long

Since DBA_SQL_PLAN_DIRECTIVES_OBJECTS wasn’t allowed we had to get creative and the dictionary table became DBA_SQL_PLAN_DIR_OBJECTS. Although this name isn’t particularly hard to remember, trying to keep abbreviations consistent across different development groups is a painful process.

So starting in Oracle Database 12c Release 2, the limit has been increased to 128 bytes for database objects. Making it much easier for the Oracle Database development teams, and all of you, to give database objects descriptive names.

SELECT product, version
 
FROM   product_component_version
WHERE  product LIKE 'Oracle%';
 
PRODUCT                                                 VERSION
 
---------------------------------------------------    ---------------
 
Oracle DATABASE 12c Enterprise Edition                  12.2.0.2.0
 
CREATE TABLE dba_sql_plan_directives_objects
(
c1 NUMBER
);
 
TABLE created.

But what exactly do I mean by a database object?

Database objects are not just tables but (sub) partitions, clusters, indexes, views, materialized views, sequences, synonyms, db links, etc.

However, not everything in the database can have a longer name. For example, tablespace names have not been increased. There is an easy way to tell which object names have been increased and which have not, by querying the corresponding data dictionary tables.

For example, USER_TABLESPACES in 12.2 looks as follows:

DESC user_tablespaces
 
Name                            NULL?    TYPE
 
------------------------------ -------- ----------------------------
 
TABLESPACE_NAME                NOT NULL VARCHAR2(30)
 
BLOCK_SIZE                     NOT NULL NUMBER
 
INITIAL_EXTENT                          NUMBER
 
NEXT_EXTENT                             NUMBER
 
MIN_EXTENTS                    NOT NULL NUMBER
 
MAX_EXTENTS                             NUMBER
 
MAX_SIZE                                NUMBER
 
PCT_INCREASE                            NUMBER
 
MIN_EXTLEN                              NUMBER
:

Finally you need to remember that the limit is now 128 bytes, not characters. So if you’re using a multi-byte character set, be careful you don’t get carried away as you may still run into an ORA-00972: identifier is too long.