FB

Have you gotten an “ORA-00972: identifier is too long” error? Learn what caused it and how to resolve it in this article.

ORA-00972 Cause

If you’re running an SQL statement that refers to another database object (table, view, sequence, synonym, and so on), you might get this error:ORA-00972

ORA-00972: identifier is too long

This error occurs because the object’s name is longer than 30 characters.

The maximum value for a name of an object in Oracle is 30 characters.

Get Your Free PDF: 9 Ways to Improve your Database Skills

For example, the error could be caused by this statement:

CREATE TABLE tbl_employee_ids_to_check_for_issues (
  ID NUMBER
);

 

SQL Error: ORA-00972: identifier is too long
00972. 00000 -  "identifier is too long"
*Cause:    An identifier with more than 30 characters was specified.
*Action:   Specify at most 30 characters.

 

ORA-00972 Solution

To resolve the ORA-00972 error, ensure the name of the object you’re working with is less than 30 characters.

This could be by creating the object with a shorter name, or altering it to ensure the new name has a shorter name.

If you shorten the name of the object (e.g. table) to less than or equal to 30 characters, it should work:

CREATE TABLE tbl_check_emp_ids (
  ID NUMBER
);

 

Table TBL_CHECK_EMP_IDS created.

 

Can You Change the Maximum Length of Object Names?

No, this cannot be changed. In Oracle 11g, and even 12cR1, the maximum length of an object name is 30 characters.

In 12cR2, the maximum value is 128 characters, if the COMPATIBLE value is set to 12.2 or higher.

 

So, that’s how you can resolve the ORA-00972 error.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your FREE PDF: "5 Ways to Avoid SQL Errors"

Get Your FREE PDF: "5 Ways to Avoid SQL Errors"