ORA-00972: Identifier Is Too Long — Causes, Fixes & Prevention
ORA-00972 is thrown by Oracle Database when an object identifier—such as a table name, column name, index name, or constraint name—exceeds the maximum allowed length. In Oracle 12c Release 1 and earlier, this limit is 30 bytes; from Oracle 12c Release 2 (12.2) onward, the limit was extended to 128 bytes. This error frequently appears during database migrations, ORM-generated DDL deployments, and manual schema design.
Top 3 Causes
1. Object Name Exceeds the 30-Byte Limit (Pre-12.2)
The most common cause. Developers writing descriptive names easily cross the 30-character boundary.
-- This will throw ORA-00972 on Oracle 12.1 and earlier
CREATE TABLE user_account_registration_detail (
user_account_registration_detail_id NUMBER PRIMARY KEY,
user_full_legal_name_description VARCHAR2(200)
);
-- ORA-00972: identifier is too long
-- Fix: shorten the identifiers
CREATE TABLE usr_acct_reg_detail (
usr_acct_reg_detail_id NUMBER PRIMARY KEY,
usr_full_name_desc VARCHAR2(200)
);
2. Migrating DDL Scripts from Other Databases
MySQL allows 64-byte identifiers and PostgreSQL also allows 63 bytes, so scripts that work perfectly there will fail in Oracle 12.1 or earlier. Use the following query to detect offending identifiers before migration:
-- Detect table names longer than 30 characters
SELECT table_name, LENGTH(table_name) AS len
FROM user_tables
WHERE LENGTH(table_name) > 30
ORDER BY len DESC;
-- Detect column names longer than 30 characters
SELECT table_name, column_name, LENGTH(column_name) AS len
FROM user_tab_columns
WHERE LENGTH(column_name) > 30
ORDER BY len DESC;
-- Detect constraint names longer than 30 characters
SELECT constraint_name, LENGTH(constraint_name) AS len
FROM user_constraints
WHERE LENGTH(constraint_name) > 30
ORDER BY len DESC;
3. Auto-Generated Names from ORM Tools (Hibernate / JPA)
ORM frameworks like Hibernate often auto-generate foreign key and index names using patterns such as FK_tablename_reftable_columnname, which quickly exceed 30 characters.
-- Auto-generated name likely to cause ORA-00972
ALTER TABLE order_detail_history
ADD CONSTRAINT fk_order_detail_history_product_master_product_id
FOREIGN KEY (product_id) REFERENCES product_master(product_id);
-- ORA-00972: identifier is too long
-- Fix: explicitly specify a short constraint name
ALTER TABLE order_detail_history
ADD CONSTRAINT fk_ord_dtl_hist_prod_id
FOREIGN KEY (product_id) REFERENCES product_master(product_id);
Quick Fix Solutions
Rename an existing column or table:
-- Rename a column (Oracle 9i R2+)
ALTER TABLE emp_detail
RENAME COLUMN employee_full_legal_name_description TO emp_full_name_desc;
-- Rename a table
RENAME old_very_long_table_name TO new_short_name;
Check your Oracle version and compatibility parameter:
-- Verify version and compatible parameter
SELECT * FROM v$version;
SHOW PARAMETER compatible;
-- If compatible >= 12.2.0, you can use identifiers up to 128 bytes
Prevention Tips
Enforce a Naming Convention Standard
Define and document maximum lengths for each object type—for example, table names ≤ 20 characters, column names ≤ 25 characters. This keeps you safe regardless of Oracle version. Integrate a DDL validation script into your CI/CD pipeline to catch violations before deployment.Automate Pre-Deployment Validation
Add an automated check that scans all DDL scripts for identifiers exceeding your threshold before they reach any environment.
-- Pre-deployment validation: flag identifiers approaching the limit
SELECT object_type,
object_name,
LENGTH(object_name) AS name_len
FROM user_objects
WHERE LENGTH(object_name) > 28 -- warn 2 chars before the hard limit
ORDER BY name_len DESC;
Following these practices eliminates ORA-00972 before it ever reaches production.
📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.
Top comments (0)