Error Description:
ORA-00910: specified length too long for its datatype
The above error message while creating table with string data types of below length:
- VARCHAR2(4001), where length is greater than 4000.
- NVARCHAR2(2001), where length is greater than 2000.
- RAW(2001), where length is greater than 2000.
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.
Solution:
There is one parameter in Oracle Database called as "MAX_STRING_SIZE" which controls the max size of VARCHAR2, NVARCHAR2, and RAW data types.
The default value of these parameters is STANDARD, where the length limit is as:
- VARCHAR2 :- 4000 bytes
- NVARCHAR2 :- 2000 bytes
- RAW :- 2000 bytes
To increase the size of this parameter, the parameter value needs to be changed from STANDARD to EXTENDED. To achieve this, below steps need to be followed.
Please perform below prerequisites before proceeding.
- The COMPATIBLE initialization parameter must be set to 12.0.0.0 or higher to set MAX_STRING_SIZE = EXTENDED.
- We can change the value of MAX_STRING_SIZE from STANDARD to EXTENDED, but revert is not possible i.e. from EXTENDED to STANDARD.
- Database Vault should be disabled before executing the steps or grant DV_PATCH_ADMIN to SYS user.
- The utl32k.sql script increases the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns for the views where this is required, not for all.
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. |
For Physical Standby Database:
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 |
For RAC Database:
If you want to change this parameter in RAC environment then follow the below steps:
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 |
I hope you enjoyed the post !
Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get future blog updates !
Thank you for your comment !