What are the maximum length of a table name and column name in Oracle?
This question is related to
oracle
name-length
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.
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
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)
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).
In Oracle 12.1 and below: 30 char (bytes, really, as has been stated).
But do not trust me; try this for yourself:
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
Updated: as stated above, in Oracle 12.2 and later, the maximum object name length is now 128 bytes.
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.
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...
The maximum name size is 30 characters because of the data dictionary which allows the storage only for 30 bytes
Oracle database object names maximum length is 30 bytes.
Object Name Rules: http://docs.oracle.com/database/121/SQLRF/sql_elements008.htm
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
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
Source: Stackoverflow.com