What is the maximum length of a table name in Oracle?
OracleName LengthOracle Problem Overview
What are the maximum length of a table name and column name in Oracle?
Oracle Solutions
Solution 1 - Oracle
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.
Solution 2 - Oracle
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)
Solution 3 - Oracle
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
Solution 4 - Oracle
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...
Solution 5 - Oracle
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
Solution 6 - Oracle
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
Solution 7 - Oracle
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.
Solution 8 - Oracle
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).
Solution 9 - Oracle
The maximum name size is 30 characters because of the data dictionary which allows the storage only for 30 bytes
Solution 10 - Oracle
Oracle database object names maximum length is 30 bytes.
Object Name Rules: http://docs.oracle.com/database/121/SQLRF/sql_elements008.htm
Solution 11 - Oracle
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
Solution 12 - Oracle
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.