218

What are the maximum length of a table name and column name in Oracle?

Gowtham Smv
  • 17
  • 2
robertkroll
  • 8,544
  • 6
  • 24
  • 24

12 Answers12

322

In Oracle 12.2 and above the maximum object name length is 128 bytes.

In Oracle 12.1 and below the maximum object name length is 30 bytes.

Dave Jarvis
  • 30,436
  • 41
  • 178
  • 315
TStamper
  • 30,098
  • 10
  • 66
  • 73
  • 12
    It's actually based on the version in the compatible parameter. If you have a 12.2 DB with compatible set to 11.2.0, is still limits you to 30 chars. – rtaft Nov 15 '18 at 15:13
249

Teach a man to fish

Notice the data-type and size

>describe all_tab_columns

VIEW all_tab_columns

Name                                      Null?    Type                        
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)                
 TABLE_NAME                                NOT NULL VARCHAR2(30)                
 COLUMN_NAME                               NOT NULL VARCHAR2(30)                
 DATA_TYPE                                          VARCHAR2(106)               
 DATA_TYPE_MOD                                      VARCHAR2(3)                 
 DATA_TYPE_OWNER                                    VARCHAR2(30)                
 DATA_LENGTH                               NOT NULL NUMBER                      
 DATA_PRECISION                                     NUMBER                      
 DATA_SCALE                                         NUMBER                      
 NULLABLE                                           VARCHAR2(1)                 
 COLUMN_ID                                          NUMBER                      
 DEFAULT_LENGTH                                     NUMBER                      
 DATA_DEFAULT                                       LONG                        
 NUM_DISTINCT                                       NUMBER                      
 LOW_VALUE                                          RAW(32)                     
 HIGH_VALUE                                         RAW(32)                     
 DENSITY                                            NUMBER                      
 NUM_NULLS                                          NUMBER                      
 NUM_BUCKETS                                        NUMBER                      
 LAST_ANALYZED                                      DATE                        
 SAMPLE_SIZE                                        NUMBER                      
 CHARACTER_SET_NAME                                 VARCHAR2(44)                
 CHAR_COL_DECL_LENGTH                               NUMBER                      
 GLOBAL_STATS                                       VARCHAR2(3)                 
 USER_STATS                                         VARCHAR2(3)                 
 AVG_COL_LEN                                        NUMBER                      
 CHAR_LENGTH                                        NUMBER                      
 CHAR_USED                                          VARCHAR2(1)                 
 V80_FMT_IMAGE                                      VARCHAR2(3)                 
 DATA_UPGRADED                                      VARCHAR2(3)                 
 HISTOGRAM                                          VARCHAR2(15)
  • 7
    With standard SQL you can also determine the lengths by querying the table: select * from all_tab_columns where table_name = 'ALL_TAB_COLUMNS'; – JustinKSU Mar 11 '16 at 23:18
  • 2
    It may be better to describe the base table [all_objects](http://docs.oracle.com/database/122/REFRN/ALL_OBJECTS.htm#REFRN20146), since that shows the name limitation applies to pretty much everything which has an all_* view based off of it. – mormegil May 22 '17 at 01:53
  • 5
    Take this answer with a grain of salt. On version 12.1.0.2.0 `describe user_mview_logs` returns `LOG_TABLE VARCHAR2(128)` but if you try anything approaching 30 characters or more for a materialized view log name, you'll get some quite puzzling results. – Saul Sep 21 '17 at 16:29
  • 4
    Despite having 128 length, you can still get stuck with the 30 char limit if your compatible param is set to an older version. – rtaft Nov 15 '18 at 15:10
20

DESCRIBE all_tab_columns

will show a TABLE_NAME VARCHAR2(30)

Note VARCHAR2(30) means a 30 byte limitation, not a 30 character limitation, and therefore may be different if your database is configured/setup to use a multibyte character set.

Mike

9

Right, but as long as you use ASCII characters even a multibyte character set would still give a limitation of exactly 30 characters... so unless you want to put hearts and smiling cats in you're DB names your fine...

Kyle
  • 17,317
  • 32
  • 140
  • 246
Maarten
  • 107
  • 1
  • 1
  • Just to make that clear: there are multi-byte character sets which DO require more than one byte per ASCII character, but they cannot be used as database character set as there is a strict "ASCII superset" requirement for them. (actually its ASCII or EBCDIC depending on the platform). – eckes Aug 03 '15 at 17:07
7

Updated: as stated above, in Oracle 12.2 and later, the maximum object name length is now 128 bytes.

The rest of this post applied to Oracle 12.1 and below: the limit was then 30 char (bytes, really).

But do not take my word for it; try this for yourself (on Oracle 12.1 or below):

SQL> create table I23456789012345678901234567890 (my_id number);

Table created.



SQL> create table I234567890123456789012345678901(my_id number);


ERROR at line 1:

ORA-00972: identifier is too long
Wild Pottok
  • 318
  • 3
  • 8
4

The schema object naming rules may also be of some use:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements008.htm#sthref723

Ian Carpenter
  • 8,346
  • 6
  • 50
  • 82
3

On Oracle 12.2, you can use built-in constant, ORA_MAX_NAME_LEN, set to 128 bytes (as per 12.2) Prior to Oracle 12.1 max size was 30 bytes.

fg78nc
  • 4,774
  • 3
  • 19
  • 32
2

The maximum name size is 30 characters because of the data dictionary which allows the storage only for 30 bytes

Annu
  • 532
  • 4
  • 8
  • 22
2

In the 10g database I'm dealing with, I know table names are maxed at 30 characters. Couldn't tell you what the column name length is (but I know it's > 30).

Harper Shelby
  • 16,475
  • 2
  • 44
  • 51
1

Oracle database object names maximum length is 30 bytes.

Object Name Rules: http://docs.oracle.com/database/121/SQLRF/sql_elements008.htm

Gerrit
  • 29
  • 3
1

I'm working on Oracle 12c 12.1. However, doesn't seem like it allows more than 30 characters for column/table names.

Read through an oracle page which mentions 30 bytes. https://docs.oracle.com/database/121/SQLRF/sql_elements008.htm#SQLRF00223

In 12c although the all_tab_columns do say VARCHAR2(128) for Table_Name, it does not allow more than 30 bytes name.

Found another article about 12c R2, which seems to be allowing this up to 128 characters. https://community.oracle.com/ideas/3338

Vaibhav
  • 123
  • 1
  • 6
-5

The maximum length of the table and column name is 128 bytes or 128 characters. This limit is for using sybase database users. I verified this answer thoroughly, so that I have posted this answer confidently.

Gowtham Smv
  • 17
  • 2