All DBAs & Developers would have always wondered that why Oracle has the identifier limit of 30 bytes whereas other rdbms SQL Server, MySQL etc have much bigger limit.

Well honestly even I don’t know that, it has been this way ever since despite multiple request from customers/developers to Oracle to increase the identifier limit, but the good news is from Oracle 12c Release 2 the max limit for identifiers is increased to 128 bytes.

So if the compatible parameter is set lower to 12.2 then names must be from 1 to 30 bytes long with following exceptions:-

1) Names of databases are limited to 8 bytes.
2) Names of database links can be as long as 128 bytes.

And if the compatible parameter is set 12.2 or higher then names must be from 1 to 128 bytes long with following exceptions:-

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

To make it even more clear, 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 whereas in previous versions the total length of the identifier can be up to 98 bytes.

For all the DBAs/Developers working on migrating projects from/to MySQL, SQL Server, Sybase etc, this small enhancement would save hours, so keep enjoying the migrations even more.