ORA-00972: identifier is too long

In Oracle 12.1 and below, the maximum length for the name of an object (Table, View, Procedure, Package, Function, Materialized View etc.) is 30 bytes (or 30 characters, if each character is of 1 byte as per character set used). Starting 12.2, name length has been increased to 128 bytes/characters. Likewise, the name of a column of a table or view should also not exceed the allowed length. In the following I am providing a few examples where we will see ORA-00972 error returned in 12.1 database

Creating a table with a name longer than 30 characters.

SQL> create table MYTABLENAMEISLONGERTHANTHIRTYCHARACTERS(id number); create table MYTABLENAMEISLONGERTHANTHIRTYCHARACTERS (id number)              * ERROR at line 1: ORA-00972: identifier is too long

Creating a table with a column with column name exceeding 30 characters.

SQL> create table T (MYCOLUMNNAMEISLONGERTHANTHIRTYCHARACTERS number); create table T(MYCOLUMNNAMEISLONGERTHANTHIRTYCHARACTERS number)                * ERROR at line 1: ORA-00972: identifier is too long

Creating a procedure having name with more than 30 characters long.

SQL> create or replace procedure MYPROCEDURENAMEISLONGERTHANTHIRTYCHARACTERS is   2  begin;   3  1:=2;   4  end;   5  / create or replace procedure P234567890123456789012345678901 is                             *

Creating a view having name more than 30 characters long.

SQL> create or replace view MYVIEWNAMEISLONGERTHANTHIRTYCHARACTERS as select * from dba_tables; create or replace view MYVIEWNAMEISLONGERTHANTHIRTYCHARACTERS as select * from dba_tables                        * ERROR at line 1: ORA-00972: identifier is too long

In a SQL or a DML, if you mention a table, view or column name longer than 30 characters, or an alias for them that is longer than 30 character, this would also return same error message.

SQL> select MYCOLUMNNAMEISLONGERTHANTHIRTYCHARACTERS from dual; select MYCOLUMNNAMEISLONGERTHANTHIRTYCHARACTERS from dual        * ERROR at line 1: ORA-00972: identifier is too long SQL> select id from MYTABLENAMEISLONGERTHANTHIRTYCHARACTERS; select id from MYTABLENAMEISLONGERTHANTHIRTYCHARACTERS                * ERROR at line 1: ORA-00972: identifier is too long