1) Introduction
In modern database environments, handling large volumes of data efficiently is a key requirement. While working with string-based data types such as VARCHAR2, NVARCHAR2, and RAW in Oracle Database, DBAs often encounter limitations related to maximum column size.
One such common issue is:
ORA-00910: specified length too long for its datatype
This error occurs when attempting to define column sizes beyond the allowed limits defined by Oracle. Understanding the root cause and resolving it properly is essential for supporting applications that require larger data storage.
This document provides a complete step-by-step guide to identifying, analyzing, and resolving this issue using the MAX_STRING_SIZE parameter in Oracle Database.
2) Error Description
The error occurs when creating a table with column sizes exceeding Oracle’s default limits:
- VARCHAR2 > 4000 bytes
- NVARCHAR2 > 2000 bytes
- RAW > 2000 bytes
Example:
CREATE TABLE TEST.TP (first_name VARCHAR2(4001));
❌ Results in:
ORA-00910: specified length too long for its datatype
3) Root Cause
By default, Oracle Database uses:
MAX_STRING_SIZE = STANDARD
This restricts maximum column sizes to:
| Data Type | Max Size (STANDARD) |
|---|---|
| VARCHAR2 | 4000 bytes |
| NVARCHAR2 | 2000 bytes |
| RAW | 2000 bytes |
Any attempt to exceed these limits results in ORA-00910.
Test Case to identify the root cause:
I created tables with below length and I could create tables, but if I tried to create tables with values greater than that i.e varchar2(4001), nvarchar2(2001), raw(2001), then I faced above error message.
- varchar2(4000)
- nvarchar2(2000)
- raw (2000)
SQL> CREATE TABLE TEST.TP_VARCHAR2 (first_name VARCHAR2(4000));
Table created.
SQL> CREATE TABLE TEST.TP_VARCHAR2 (first_name varchar2(4001));
CREATE TABLE TEST.TP (first_name varchar2(4001))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype
SQL> CREATE TABLE TEST.TP_NVARCHAR2 (address NVARCHAR2(2000));
Table created.
SQL> CREATE TABLE TEST.TP_NVARCHAR2 (address NVARCHAR2(2001));
CREATE TABLE TEST.TP (address NVARCHAR2(2001))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype
SQL> CREATE TABLE TEST.TP_RAW (comment1 raw(2000));
Table created.
SQL> CREATE TABLE TEST.TP_RAW (comment1 RAW(2001));
CREATE TABLE TEST.TP (comment1 raw(2001))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype
From the above conclusion, it is clear that there is defined limit at DB level beyond which we can not create a table with these data types
4) Solution Overview
To overcome this limitation, Oracle provides an option to extend the maximum size using:
MAX_STRING_SIZE = EXTENDED
This allows:
| Data Type | Max Size (EXTENDED) |
|---|---|
| VARCHAR2 | 32767 bytes |
| NVARCHAR2 | 32767 bytes |
| RAW | 32767 bytes |
5) Prerequisites
Before proceeding, ensure the following:
- COMPATIBLE parameter ≥ 12.0.0.0
- Full database backup is taken
- Oracle Database Vault is disabled (or proper privileges granted)
- Change is irreversible (cannot revert to STANDARD)
6) Implementation Steps (Standalone Database)
Step 1: Check DV is enabled in the database and database version.
SQL> set lines 300 pages 3000
SQL> col parameter for a26
SQL> col value for a26
SQL> select parameter,value from v$option where parameter like '%Oracle%';
PARAMETER VALUE
---------------------- --------
Oracle Data Guard TRUE
Oracle Label Security FALSE
Oracle Database Vault FALSE ------> DV is disabled.
SQL> select banner from v$version;
BANNER
-----------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> show parameter compatible
NAME TYPE VALUE
------------- ----------- --------
compatible string 19.0.0
SQL> show parameter max_string_size
NAME TYPE VALUE
------------------- ----------- -----------
max_string_size string STANDARD
Step 2: Take full database backup. This is why because if any error occurs during DB startup post this script execution or during the script execution due to Database Vault or any other unknown reason then we can not start the Database.
Step 3: Shutdown the database and start the same in upgrade mode.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 5066718192 bytes
Fixed Size 9038832 bytes
Variable Size 922746880 bytes
Database Buffers 4127195136 bytes
Redo Buffers 7737344 bytes
Database mounted.
Database opened.
SQL> select instance_name,status,logins from v$instance;
INSTANCE_NAME STATUS LOGINS
---------------- ------------ ----------
max OPEN MIGRATE RESTRICTED
Step 4: Set the max_string_size parameter to extended and run 32k script.
SQL> alter system set MAX_STRING_SIZE=EXTENDED;
System altered.
SQL> @?/rdbms/admin/utl32k.sql
Step 5: Once the above script execution gets completed then stop the database and take normal startup.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5066718192 bytes
Fixed Size 9038832 bytes
Variable Size 922746880 bytes
Database Buffers 4127195136 bytes
Redo Buffers 7737344 bytes
Database mounted.
Database opened.
Step 6: Perform post checks.
SQL> show parameter MAX_STRING_SIZE
NAME TYPE VALUE
-------------------- ----------- ----------
max_string_size string EXTENDED
SQL> CREATE TABLE TEST.TP_VARCHAR2 (first_name varchar2(4001));
Table created.
SQL> CREATE TABLE TEST.TP_NVARCHAR2 (address NVARCHAR2(2001));
Table created.
SQL> CREATE TABLE TEST.TP_RAW (comment1 raw(2001));
Table created.
7) Implementation for Physical Standby
The above steps have been performed on Primary database, but what about Physical Standby database ? No need to execute too many steps on Physical Standby database. Just simply execute below fewer steps to set this parameter in Physical Standby database.
Step 1: Stop the database and start it in upgrade mode.
SQL> shut immediate;
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 5066718192 bytes
Fixed Size 9038832 bytes
Variable Size 922746880 bytes
Database Buffers 4127195136 bytes
Redo Buffers 7737344 bytes
Database mounted.
Database opened.
Step 2: Set the parameter max_string_size to EXTENDED.
SQL> alter system set MAX_STRING_SIZE=EXTENDED;
System altered.
Step 3: Stop the database and start the same mount mode.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5066718192 bytes
Fixed Size 9038832 bytes
Variable Size 922746880 bytes
Database Buffers 4127195136 bytes
Redo Buffers 7737344 bytes
Database mounted.
SQL> show parameter MAX_STRING_SIZE
NAME TYPE VALUE
-------------------- ----------- ----------
max_string_size string EXTENDED
8) Implementation for RAC Environment
Step 1: Set the cluster_database parameter to FALSE and stop the entire database by srvctl.
SQL> alter system set cluster_database = false scope = spfile;
$srvctl stop db -d max
$srvctl status db -d max
Step 2: From any once instance login as sqlplus and start the instance in upgrade mode.
SQL> startup upgrade;
Step 3: Set the parameter to EXTENDED value and execute 32k script.
SQL> show parameter MAX_STRING_SIZE
SQL> alter system set MAX_STRING_SIZE=EXTENDED;
SQL> @?/rdbms/admin/utl32k.sql
Step 4: Set the cluster_database to TRUE and take restart of the database.
SQL> alter system set cluster_database = true scope = spfile;
SQL> shut immediate;
$srvctl start db -d max
$srvctl status db -d max
9) Key Observations
- The change updates metadata using
utl32k.sql - Required for applications needing larger column sizes
- Not reversible once enabled
- Works across Standalone, RAC, and Data Guard environments
10) Conclusion
The ORA-00910 error is a common limitation encountered due to default string size restrictions in Oracle Database. By enabling the MAX_STRING_SIZE = EXTENDED parameter, DBAs can significantly increase column size limits and support modern application requirements.
This approach provides a safe and structured way to extend database capabilities while maintaining performance and stability across different environments.