一、11G and before

在使用11G数据库时，经常会遇到报错ORA-00972，原因是因为对象名称定义太长，限制为30位字符.


Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table NUS_DGSGDJD_SDSDWQDD_ADSADAD20210411(ID NUMBER);
create table NUS_DGSGDJD_SDSDWQDD_ADSADAD20210411(ID NUMBER)
             *
ERROR at line 1:
ORA-00972: identifier is too long


SQL> select length('NUS_DGSGDJD_SDSDWQDD_ADSADAD20210411') from dual;

LENGTH('NUS_DGSGDJD_SDSDWQDD_ADSADAD20210411')
----------------------------------------------
					    36

SQL> desc user_tables
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME				   NOT NULL VARCHAR2(30)
 TABLESPACE_NAME				    VARCHAR2(30)
 CLUSTER_NAME					    VARCHAR2(30)
 IOT_NAME					        VARCHAR2(30)

SQL> desc sys.obj$
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#					   NOT NULL NUMBER
 DATAOBJ#					        NUMBER
 OWNER# 				   NOT NULL NUMBER
 NAME					   NOT NULL VARCHAR2(30)


[oracle@orcl11g:/home/oracle]$ oerr ORA 00972
00972, 00000, "identifier is too long"
// *Cause:  An identifier with more than 30 characters was specified.
// *Action:  Specify at most 30 characters.
二、12C and later

可以看到，在12C之后的版本，Oracle调整对象名称为128位字符，但是密码依然保持30位。

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create table NUS_DGSGDJD_SDSDWQDD_ADSADAD20210411(ID NUMBER);

Table created.

SQL> select length('NUS_DGSGDJD_SDSDWQDD_ADSADAD20210411') from dual;

LENGTH('NUS_DGSGDJD_SDSDWQDD_ADSADAD20210411')
----------------------------------------------
					    36

SQL> desc user_tables
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME				   NOT NULL VARCHAR2(128)
 TABLESPACE_NAME				    VARCHAR2(30)
 CLUSTER_NAME					    VARCHAR2(128)
 IOT_NAME					        VARCHAR2(128)
 
SQL> desc sys.obj$
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#					   NOT NULL NUMBER
 DATAOBJ#					        NUMBER
 OWNER# 				   NOT NULL NUMBER
 NAME					   NOT NULL VARCHAR2(128)


[oracle@orcl:/home/oracle]$ oerr ORA 00972
00972, 00000, "identifier is too long"
// *Cause:  An identifier with more than 128 bytes was specified,
//          or a password identifier longer than 30 bytes was specified.
// *Action:  Specify at most 128 bytes for identifiers,
//           and at most 30 bytes for password identifiers.