Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Maximum length of table and column names

Re: Maximum length of table and column names

From: Mark D Powell <>
Date: 1 Oct 2004 08:13:41 -0500
Message-ID: <1d0befc67c306caec19513e9f7cd44cb$>

Jens Riedel wrote:

> Hello,

> I'm trying to port an application from a mySQL database to Oracle 9.
> I have problems to create some tables because the table or column names
> seem to be too long.
> I found the following:

> ----------------------
> ORA-00972: identifier is too long

> Cause: The name of a schema object exceeds 30 characters. Schema objects
> are tables, clusters, views, indexes, synonyms, tablespaces, and usernames.

> Action: Shorten the name to 30 characters or less.
> ------------------------

> My question: is a column name also limited to 30 characters and is there
> a possibility to change these settings instead of shortening the table
> and column names?

> Thanks and regards,
> Jens

Jens, yes the column name is limited to 30 characters. No, you cannot change this.

I encourage the use of short table names generally limiting them to around 20 characters so that indexes can be named as table_name_pk, _idx1, _idx2, etc...

Then if an audit/history trail is later determined to be needed there is room to name the audit table as table_name_hist and still follow the same naming convention for the indexes.

Any naming scheme to use the column names as part of the index will quickly break down or become confusing as the number of tables grows and the same column name needs to be indexed in different tables.

To be able to define short names you should always abbrebiate any term that has to be abbrebiated anywhere, everywhere that it appears. That is if you use hist for history in one table use hist for history in all tables even if you have room under the 30 characters limintation to spell it out.

Just my opinion.
-- Mark D Powell --


Received on Fri Oct 01 2004 - 08:13:41 CDT

Original text of this message