Oracle Database 12c Release 2 - Long Identifiers

Prior to Oracle Database 12c Release 2 have a problem with long identifier name, for example, in 11.2 version you cannot create long name (max 30 Bytes) tables.  As you see, from screenshots length of table name column is maximum 30.

Creating table with long name

Table Name Column size 30

In Oracle Database 12c Release 2 changed the length of identifier names, and this change depends on the COMPATIBLE initialization parameter. Documentation says:

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. read more

I  created long name table, as below

Create Table with long name

Shows in user tables view

Maximum length of table is 128 bytes
If you set COMPATIBLE initialization parameter to lower value 12.2, then you will get the error about ORA-00972: identifier is long

Best Regards
Mahir M. Quluzade


Popular posts from this blog

Oracle Data Guard 11g - How to safely remove a Data Guard Broker configuration?

Oracle Database 18c - Install On-Premises for Linux

Cluvfy error : Framework setup check failed on all the nodes