ORA-00972 Identifier Is Too Long - Complete Resolution Guide

ORA-00972: Identifier Is Too Long

Error Overview

Error Text: ORA-00972: identifier is too long

The ORA-00972 error occurs when you attempt to create or reference a database object with a name that exceeds Oracle’s maximum identifier length. This limit varies by Oracle version.

Identifier Length Limits

Oracle Version	Maximum Length	Notes
Oracle 12.1 and earlier	30 bytes	Traditional limit
Oracle 12.2 and later	128 bytes	Extended identifiers enabled by default

Important: The limit is in bytes, not characters. Multi-byte characters (UTF-8) use 2-4 bytes each.

Common Causes

1. Exceeding Name Length Limit

• Table names too long
• Column names too long
• Index or constraint names too long
• PL/SQL variable names too long

2. Auto-Generated Names

• System-generated constraint names
• Index names from ORM tools
• Hibernate/JPA generated identifiers

3. Multi-Byte Character Impact

• Using non-ASCII characters in names
• UTF-8 encoded special characters

4. Version Compatibility Issues

• Code written for 12.2+ running on 12.1 or earlier
• Database links to older databases

Error Examples and Solutions

Example 1: Table Name Too Long

-- ERROR: Name exceeds 30 bytes (pre-12.2)
CREATE TABLE employee_performance_evaluation_records_historical (
  id NUMBER
);
-- ORA-00972: identifier is too long

-- Character count: 51 characters
-- In pre-12.2: limit is 30

-- SOLUTION: Shorten the name
CREATE TABLE emp_perf_eval_records_hist (
  id NUMBER
);

-- Or use meaningful abbreviations
CREATE TABLE employee_perf_eval_hist (
  id NUMBER
);

Example 2: Column Name Too Long

-- ERROR: Column name too long
CREATE TABLE employees (
  employee_identification_number_primary NUMBER,
  date_of_last_performance_review DATE
);
-- ORA-00972: identifier is too long

-- SOLUTION: Use shorter column names
CREATE TABLE employees (
  emp_id NUMBER,
  last_review_date DATE
);

-- Add comments for documentation
COMMENT ON COLUMN employees.emp_id IS 'Employee identification number (primary key)';
COMMENT ON COLUMN employees.last_review_date IS 'Date of last performance review';

Example 3: Constraint Name Too Long

-- ERROR: Constraint name too long
ALTER TABLE order_items ADD CONSTRAINT
  fk_order_items_product_catalog_product_id
  FOREIGN KEY (product_id) REFERENCES product_catalog(product_id);
-- ORA-00972: identifier is too long

-- SOLUTION: Use shorter constraint name
ALTER TABLE order_items ADD CONSTRAINT
  fk_oitm_prod_id
  FOREIGN KEY (product_id) REFERENCES product_catalog(product_id);

-- Naming convention: fk_<table_abbrev>_<column_abbrev>

Example 4: Index Name Too Long

-- ERROR: Index name too long
CREATE INDEX idx_customer_orders_order_date_customer_id_status
ON customer_orders(order_date, customer_id, status);
-- ORA-00972: identifier is too long

-- SOLUTION: Shorter index name
CREATE INDEX idx_custord_dt_cust_stat
ON customer_orders(order_date, customer_id, status);

Example 5: PL/SQL Variable Too Long

-- ERROR: Variable name too long
DECLARE
  v_employee_last_performance_review_date DATE;
BEGIN
  NULL;
END;
/
-- ORA-00972: identifier is too long

-- SOLUTION: Use shorter variable name
DECLARE
  v_last_review_date DATE;  -- Or v_emp_last_perf_dt
BEGIN
  NULL;
END;
/

Example 6: Database Link Name Too Long

-- ERROR: Database link name too long
CREATE DATABASE LINK production_reporting_database_primary_link
CONNECT TO user IDENTIFIED BY password
USING 'prod_db';
-- ORA-00972: identifier is too long

-- SOLUTION: Shorter link name
CREATE DATABASE LINK prod_report_primary
CONNECT TO user IDENTIFIED BY password
USING 'prod_db';

Checking Your Oracle Version and Limit

-- Check Oracle version
SELECT banner FROM v$version WHERE banner LIKE 'Oracle%';

-- Check maximum identifier length (12.2+)
SELECT value FROM v$parameter WHERE name = 'max_string_size';

-- Check compatible parameter
SHOW PARAMETER compatible;

Extended Identifiers (12.2+)

Oracle 12.2 introduced 128-byte identifiers:

-- Check if extended identifiers are enabled (12.2+)
SELECT value FROM v$parameter WHERE name = 'max_string_size';
-- If 'EXTENDED', 128-byte identifiers are supported

-- Example: This works in 12.2+ but fails in 12.1
CREATE TABLE this_is_a_very_long_table_name_that_exceeds_thirty_characters (
  id NUMBER
);
-- Works in 12.2+, fails in 12.1 and earlier

Naming Convention Best Practices

Recommended Abbreviations

Full Term	Abbreviation
employee	emp
department	dept
customer	cust
product	prod
order	ord
transaction	txn
number	num
identifier	id
description	desc
date	dt
amount	amt
status	stat
primary	pri
foreign	fk
index	idx
sequence	seq

Naming Patterns

-- Tables: singular noun, abbreviated if needed
employees, emp_assignments, dept_budgets

-- Primary keys: pk_<table>
pk_employees, pk_emp_assignments

-- Foreign keys: fk_<child_table>_<parent_table> or fk_<child>_<column>
fk_emp_dept, fk_orders_customers

-- Indexes: idx_<table>_<columns>
idx_emp_name, idx_ord_date_status

-- Sequences: <table>_seq or seq_<table>
employees_seq, seq_orders

-- Check constraints: chk_<table>_<rule>
chk_emp_salary, chk_ord_status

-- Unique constraints: uk_<table>_<columns>
uk_emp_email, uk_prod_code

Character Counting Helper

-- Check length of potential identifier
SELECT LENGTH('your_potential_identifier_name') as char_length,
       LENGTHB('your_potential_identifier_name') as byte_length
FROM dual;

-- For Oracle 12.1 and earlier: must be <= 30 bytes
-- For Oracle 12.2 and later: can be <= 128 bytes

Handling Auto-Generated Names

Hibernate/JPA Configuration

// In persistence.xml or application.properties
// Limit identifier length for older Oracle versions
hibernate.physical_naming_strategy=com.example.Oracle12cNamingStrategy

// Custom naming strategy to truncate long names
public class Oracle12cNamingStrategy implements PhysicalNamingStrategy {
    @Override
    public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment context) {
        return truncate(name, 30);
    }

    private Identifier truncate(Identifier identifier, int maxLength) {
        String name = identifier.getText();
        if (name.length() > maxLength) {
            return Identifier.toIdentifier(name.substring(0, maxLength));
        }
        return identifier;
    }
}

Flyway/Liquibase Migrations

<!-- Liquibase: Use short names explicitly -->
<createTable tableName="emp_perf_hist">
    <column name="id" type="NUMBER"/>
    <column name="emp_id" type="NUMBER"/>
</createTable>

<addForeignKeyConstraint
    constraintName="fk_eph_emp"
    baseTableName="emp_perf_hist"
    baseColumnNames="emp_id"
    referencedTableName="employees"
    referencedColumnNames="id"/>

Finding Long Identifiers in Your Schema

-- Find tables with long names
SELECT table_name, LENGTH(table_name) as name_length
FROM user_tables
WHERE LENGTH(table_name) > 25
ORDER BY name_length DESC;

-- Find columns with long names
SELECT table_name, column_name, LENGTH(column_name) as name_length
FROM user_tab_columns
WHERE LENGTH(column_name) > 25
ORDER BY name_length DESC;

-- Find constraints with long names
SELECT table_name, constraint_name, LENGTH(constraint_name) as name_length
FROM user_constraints
WHERE LENGTH(constraint_name) > 25
ORDER BY name_length DESC;

-- Find indexes with long names
SELECT table_name, index_name, LENGTH(index_name) as name_length
FROM user_indexes
WHERE LENGTH(index_name) > 25
ORDER BY name_length DESC;

Renaming Existing Objects

-- Rename table
ALTER TABLE old_long_table_name RENAME TO short_name;

-- Rename column (Oracle 9i+)
ALTER TABLE employees RENAME COLUMN
  old_very_long_column_name TO short_col;

-- Rename constraint
ALTER TABLE employees RENAME CONSTRAINT
  old_constraint_name TO new_name;

-- Rename index
ALTER INDEX old_index_name RENAME TO new_idx;

Multi-Byte Character Considerations

-- UTF-8 characters can use multiple bytes
-- This might fail even if character count is under 30:
SELECT LENGTHB('日本語テーブル名') as bytes FROM dual;
-- Returns 24 bytes for 8 characters

-- Check database character set
SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

-- If using AL32UTF8, Asian characters use 3 bytes each
-- Example: 日 = 3 bytes in UTF-8

Compatibility Considerations

Cross-Version Compatibility

-- When developing for multiple Oracle versions:
-- 1. Use 30-byte limit as lowest common denominator
-- 2. Or check version and use appropriate limit:

DECLARE
  v_max_length NUMBER;
BEGIN
  SELECT CASE
    WHEN version >= '12.2' THEN 128
    ELSE 30
  END INTO v_max_length
  FROM v$instance;

  DBMS_OUTPUT.PUT_LINE('Max identifier length: ' || v_max_length);
END;
/

Database Link Considerations

-- When accessing older databases via database link
-- The remote database's limit applies

-- If local is 12.2 (128 byte) but remote is 11g (30 byte):
SELECT * FROM long_table_name@old_database_link;
-- May fail if table name exceeds 30 bytes

Quick Reference

Object Type	Naming Convention	Example
Table	noun, singular	employees
Column	descriptive, short	hire_date
Primary Key	pk_table	pk_employees
Foreign Key	fk_child_parent	fk_emp_dept
Unique	uk_table_cols	uk_emp_email
Check	chk_table_rule	chk_emp_salary
Index	idx_table_cols	idx_emp_name
Sequence	table_seq	employees_seq
Trigger	trg_table_action	trg_emp_audit
View	vw_name	vw_emp_details

Related Errors

• ORA-00904 - Invalid identifier
• ORA-00911 - Invalid character
• ORA-02449 - Unique/primary keys referenced by foreign keys

Summary

1. Know your Oracle version - 30 bytes (pre-12.2) vs 128 bytes (12.2+)
2. Use meaningful abbreviations - Consistent naming conventions
3. Count bytes, not characters - Important for multi-byte charsets
4. Plan for compatibility - Use 30 bytes for cross-version support
5. Configure ORM tools - Limit auto-generated name lengths
6. Document full names - Use comments for long descriptive names