Why are Oracle table/column/index names limited to 30 characters?

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

I believe it's the ANSI standard.

EDIT:

Actually, I think it's the SQL-92 standard.

A later version of the standard appears to optionally allow for 128 character names, but Oracle doesn't yet support this (or has partial support for it, insofar as it allows 30 characters. Hmmm.)

Search for "F391, Long identifiers" on this page... http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/ap%5Fstandard%5Fsql001.htm

(Looking for a ref)

Answer 2

In addition to cagcowboy's point that it derives from the SQL standard (historically, I suspect that Oracle's decision lead to the SQL standard since Oracle predated the standardization of SQL), I would wager that a large part of the reluctance to allow longer identifiers comes from the realization that there are millions of DBAs with millions of custom scripts that all assume that identifiers are 30 characters long. Allowing every line of code that goes something like

  l_table_name VARCHAR2(30);
BEGIN
  SELECT table_name
    INTO l_table_name
    FROM dba_tables
   WHERE ...

to suddenly break because the DBA 15 years ago used VARCHAR2(30) rather than DBA_TABLES.TABLE_NAME%TYPE in the script would cause massive revolt. I would wager that Oracle alone has thousands of places where this sort of thing has been done over the years in various packages and components. Retrofitting all that existing code to support longer identifiers would be a tremendous project that would almost certainly generate way more costs in developer time, QA time, and newly introduced bugs than it would generate benefits.

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

ok, the limitation exists....

but do you really NEED more than to 30 character to name a table/index/column??

when writing queries, with that limitation I STILL find some column/table names annoying. If the limit were higher I might run into tables that required a query like:

select unique_identifier_column, 
time_when_the_user_remembered_to_change_the_row_in_the_receipt_table, 
foreign_key_to_the_ap_invoice_distributions_history_table_related_to_the_all_rows_table 
from ap_invoices_really_really_all_all_rows_present_in_this_ebs_table.

I apologize for the huge words :P

Answer 5

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.