ORACLE12.2以上版本 对象名长度限制超过30个字符

郑全

数据库兼容级别在12.2以前，对象名长度只能低于31个字符， 在12.2以上，可以达到128个字符。

SQL> create table test_128_abcdefghijklmnopqrstuvwxyz_abcdefghijklmnopqrstuvwxyz_abcdefghijklmnopqrstuvwxyz_abcdefghijklmnopqrstuvwxyz_abcdefghij(empid number);

Table created.

SQL> select table_name,length(table_name) from dba_tables where length(table_name)>100;

TABLE_NAME
--------------------------------------------------------------------------------
LENGTH(TABLE_NAME)
------------------
TEST_128_ABCDEFGHIJKLMNOPQRSTUVWXYZ_ABCDEFGHIJKLMNOPQRSTUVWXYZ_ABCDEFGHIJKLMNOPQ
RSTUVWXYZ_ABCDEFGHIJKLMNOPQRSTUVWXYZ_ABCDEFGHIJ
               127


SQL> create table test_128_abcdefghijklmnopqrstuvwxyz_abcdefghijklmnopqrstuvwxyz_abcdefghijklmnopqrstuvwxyz_abcdefghijklmnopqrstuvwxyz_abcdefghijk(empid number);

Table created.

SQL> select table_name,length(table_name) from dba_tables where length(table_name)>100;

TABLE_NAME
--------------------------------------------------------------------------------
LENGTH(TABLE_NAME)
------------------
TEST_128_ABCDEFGHIJKLMNOPQRSTUVWXYZ_ABCDEFGHIJKLMNOPQRSTUVWXYZ_ABCDEFGHIJKLMNOPQ
RSTUVWXYZ_ABCDEFGHIJKLMNOPQRSTUVWXYZ_ABCDEFGHIJ
               127

TEST_128_ABCDEFGHIJKLMNOPQRSTUVWXYZ_ABCDEFGHIJKLMNOPQRSTUVWXYZ_ABCDEFGHIJKLMNOPQ
RSTUVWXYZ_ABCDEFGHIJKLMNOPQRSTUVWXYZ_ABCDEFGHIJK
               128


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


SQL> show parameter compatible


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      19.0.0


附 mos Doc ID 2405749.1

Create Long Identifier Object Name With More Than 30 Bytes (Doc ID 2405749.1)        To BottomTo Bottom       

In this Document

Goal

Solution

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later

Oracle Cloud Infrastructure - Database Service - Version N/A and later

Oracle Database Backup Service - Version N/A and later

Oracle Database Cloud Service - Version N/A and later

Oracle Database - Standard Edition - Version 12.1.0.2 to 12.2.0.1 [Release 12.1 to 12.2]

Information in this document applies to any platform.

GOAL

Can we create long identifier object name with more than 30 bytes?

SOLUTION

Long identifier limitation is increased to 128 in 12.2. But in 12.1, its 30 and no work around for this.

For 12.2:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Database-Object-Names-and-Qualifiers.html

If COMPATIBLE is set to a value of 12.2 or higher, then names must be from 1 to 128 bytes long with these exceptions:

Names of databases are limited to 8 bytes.

Names of disk groups, pluggable databases (PDBs), rollback segments, tablespaces, and tablespace sets are limited to 30 bytes.

If an identifier includes multiple parts separated by periods, then each attribute can be up to 128 bytes long. Each period separator, as well as any surrounding double quotation marks, counts as one byte. For example, suppose you identify a column like this:

"schema"."table"."column"

The schema name can be 128 bytes, the table name can be 128 bytes, and the column name can be 128 bytes. Each of the quotation marks and periods is a single-byte character, so the total length of the identifier in this example can be up to 392 bytes.

For 12.1:

https://docs.oracle.com/database/121/SQLRF/sql_elements008.htm#SQLRF51129

Names must be from 1 to 30 bytes long with these exceptions:

Names of databases are limited to 8 bytes.

Names of database links can be as long as 128 bytes.

If an identifier includes multiple parts separated by periods, then each attribute can be up to 30 bytes long. Each period separator, as well as any surrounding double quotation marks, counts as one byte. For example, suppose you identify a column like this:

"schema"."table"."column"

The schema name can be 30 bytes, the table name can be 30 bytes, and the column name can be 30 bytes. Each of the quotation marks and periods is a single-byte character, so the total length of the identifier in this example ca