ORA-00972: identifier is too long - Oracle Error - Solution

ORA-00972: identifier is too long 

Introduction:

In this tutorial, We will learn about error "ORA-00972: identifier is too long" in oracle. When this will happen and how to resolve it.

Error: 

When you encounter error code ORA-00972, a message saying appears "identifier is too long." This error is self explanative that is some problem with length of identifier.

ORA-00972: identifier is too long 

Reason:

An identifier with more than 30 characters was specified.

Example:

See the below SQL to create the table with name  "JAVA_W3SCHOOLS_BLOG_ORACLE_TABLE".

Creating table:

CREATE TABLE JAVA_W3SCHOOLS_BLOG_ORACLE_TABLE(
  ID NUMBER
);

When we run this SQL, immediately will throw the error.

SQL> CREATE TABLE JAVA_W3SCHOOLS_BLOG_ORACLE_TABLE(
  2  ID NUMBER
  3  );

ERROR at line 1:
ORA-00972: identifier is too long

Saying identifier at line number 1 is causing for this error. There identifier is table name which is JAVA_W3SCHOOLS_BLOG_ORACLE_TABLE in our case and length is 32 characters causing for ORA-00972 error code.
Many users find themselves encountering ORA-00972 when attempting to access a table that has a large table name or column name length is more than expected.

Solution:

To resolve this error, All identifiers length should not exceed 30 characters. Refer the below modified table Creation Script where table name length is changed to 26 characters.

SQL> CREATE TABLE JAVA_W3SCHOOLS_BLOG_ORACLE(
  2  ID NUMBER
  3  );

Table created.

Special Note:

All the below are part of identifiers in oracle. Any one of these identifier length exceeds 30 characters then will see "ORA-00972" error saying "identifier is too long"

Table name
Column name
Constraint name
Partition name
Cluster name
View name
Index name
Synonym name
Table-space
Username 
Etc

Conclusion:

We learnt how to resolve the oracle error ORA-00972 which is faced by many oracle developers. We must make all identifiers length should be below or equal to 30 characters.

Oracle 12.1 was still limited to 30 characters. Starting with Oracle Database 12c Release 2 (12.2), the maximum length of identifier names for most types of database objects has been increased to 128 bytes.