Reputation: 8774

What is the maximum length of a table name in Oracle?

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

Upvotes: 222

Answers (12)

Wild Pottok

Reputation: 336

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

Upvotes: 7

TStamper

Reputation: 30354

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.

Upvotes: 325

fg78nc

Reputation: 5232

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.

Upvotes: 3

Naga

Reputation: 132

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

Upvotes: 1

Annu

Reputation: 562

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

Upvotes: 3

Gowtham Smv

Reputation: 17

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.

Upvotes: -5

Gerrit

Reputation: 29

Oracle database object names maximum length is 30 bytes.

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

Upvotes: 1

Maarten

Reputation: 117

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...

Upvotes: 10

Michael OShea

Reputation:

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

Upvotes: 20

Ian Carpenter

Reputation: 8626

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

Upvotes: 4

Mark Brady

Reputation:

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)

Upvotes: 254

Harper Shelby

Reputation: 16585

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).

Upvotes: 2

What is the maximum length of a table name in Oracle?

Answers (12)

Teach a man to fish

Related Questions