Why are Oracle table/column/index names limited to

Question

I can understand that many years ago there would be this kind of limitation, but nowadays surely this limit could easily be increased. We have naming conventions for objects, but there is always a case that turns up where we hit this limit - especially in naming foreign keys.

Does anybody actually know why this isn't a bigger size - or is it bigger in 11g?

---

Apparently the answer is that it will break currently scripts that aren't defensively coded. I say that is a very worrying thing, Oracle is trying to be the database, surely this is the kind of thing that you must constantly improve, otherwise your product will die the death of a thousand cuts.

Whenever I see this kind of objection in-house, I think it is time to bite the bullet and sort it out. If people are running scripts that they do not check or maintain when they upgrade Oracle versions, then let them suffer the consequences of that choice. Provide them a compatibility flag, up the size to 4000, then save me the wasted time when I'm creating objects of having to constantly count to 30 to check the name is 'OK'.

Answer 1

Given the practical necessity of identifier length limits, good design restricts the length of actual names to avoid hitting the ceiling when the names are combined with each other and with prefixes and suffixes.

For example, a convention of naming foreign key constraints

FK_<table1>_<table2> 

limits table names to 13 characters or less; most databases are going to need more prefixes and suffixes, further limiting the length of table names.

Answer 2

I believe that the 30 character identifier length comes from COBOL which was standardised in the late 1950s. Since COBOL programs were the main user of SQL (and SEQUEL before that (and QUEL before that)), this must have seemed like a reasonable number for the identifier length.

Answer 3

Constraint violations get reported in SQLERRM which is limited to 255 characters, and which most clients use to make errors visible. I suspect increasing the allowable size of constraint names significantly would impact the ability to report on the violations (especially where a constraint violation has been bubbled up through a few layers of PL/SQL code).

Answer 4

I was looking this up and found this question via Google, but also found out that as of Oracle 12c Release 2 (12.2), this is no longer strictly the case. (https://oracle-base.com/articles/12c/long-identifiers-12cr2)

At some point every DBA or developer will have hit a point where the 30 character limit for object names has caused a problem. This limit can be extremely painful when doing migration projects from SQL Server or MySQL to Oracle. In Oracle Database 12cR2, the maximum length of most identifiers is now 128 characters.

This is a new feature in 12.2, according to (http://blog.dbi-services.com/oracle-12cr2-long-identifiers/). According to that post, 12.1 was still limited to 30 characters.

---

Edit: Here's a link to the official Oracle documentation explaining the change. (https://docs.oracle.com/cloud/latest/exadataexpress-cloud/CSDBF/longer-identifier-names.htm#CSDBF-GUID-F4CA155F-5A37-4705-8443-0A8C9E3F875C)

Starting with Oracle Database 12c Release 2 (12.2), the maximum length of identifier names for most types of database objects has been increased to 128 bytes.