ORA-00972: identifier is too long

In Oracle, the maximum length for the name of an object (Table, View, Procedure, Package, Function, Materialized View etc.) is 30 characters. Likewise, the name of a column of a table or view should also not exceed 30 characters. In the following I am providing a few examples where we will see ORA-00972 error.

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