This parameter was introduced in Oracle 12c. MAX_STRING_SIZE controls the maximum size of string size in Oracle database.
Either we can set it to STANDARD or.EXTENDED The default value is STANDARD
MAX_STRING_SIZE = STANDARD means the maximum size of strings is 4000 bytes for VARCHAR2 and NVARCHAR2
MAX_STRING_SIZE = EXTENDED means maximum size can be upto 32767 byte .
We can change the value of MAX_STRING_SIZE from STANDARD to EXTENDED. But not from EXTENDED to STANDARD.
With MAX_STRING_SIZE set to STANDARD , if we try to set the length of column more than 4000, then it will throw ORA-00910 error.
SQL> SHOW PARAMETER MAX_STRING_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string STANDARD
SQL>
SQL> create table UNIVERSITY ( COLLEGE_NAME VARCHAR2(8000));
create table UNIVERSITY ( COLLEGE_NAME VARCHAR2(8000))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype
STEPS for converting MAX_STRING_SIZE to EXTENDED:
1. Start database in upgrade mode:
SQL> SHUTDOWN IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. SQL>STARTUP UPGRADE ORACLE instance started. Total System Global Area 1.4663E+10 bytes Fixed Size 15697000 bytes Variable Size 1.1878E+10 bytes Database Buffers 2717908992 bytes Redo Buffers 51404800 bytes Database mounted. Database opened.
2. Change the value to EXTENDED
SQL> alter system set MAX_STRING_SIZE='EXTENDED' SCOPE=BOTH; System altered. SQL> show parameter MAX_STRING_SIZE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_string_size string EXTENDED
3.Run utl32k.sql script:
@?/rdbms/admin/utl32k.sql
SQL> @?/rdbms/admin/utl32k.sql Session altered. DOC>####################################################################### DOC>####################################################################### DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error if the database has not been opened for UPGRADE. DOC> DOC> Perform a "SHUTDOWN ABORT" and DOC> restart using UPGRADE. DOC>####################################################################### DOC>####################################################################### DOC># no rows selected DOC>####################################################################### DOC>####################################################################### DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error if the database does not have compatible >= 12.0.0 DOC> DOC> Set compatible >= 12.0.0 and retry. DOC>####################################################################### DOC>####################################################################### DOC># PL/SQL procedure successfully completed. Session altered. 1524 rows updated. Commit complete. System altered. PL/SQL procedure successfully completed. Commit complete. System altered. Session altered. Session altered. Table created. Table created. Table created. Table truncated. 0 rows created. PL/SQL procedure successfully completed. no rows selected DOC>####################################################################### DOC>####################################################################### DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error if we encountered an error while modifying a column to DOC> account for data type length change as a result of enabling or DOC> disabling 32k types. DOC> DOC> Contact Oracle support for assistance. DOC>####################################################################### DOC>####################################################################### DOC># PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Commit complete.
4.Restart the database:
shutdown immediate; startup SQL> show parameter max_string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_string_size string EXTENDED
Let’s create a table with length more than 4000.
SQL> create table UNIVERSITY ( COLLEGE_NAME VARCHAR2(8000)); Table created.
COMMON ISSUES:
1. If you try to restart the database without running utl32k.sql script, got below error.
Database dismounted. ORACLE instance shut down. SQL> ORACLE instance started. Total System Global Area 1.4663E+10 bytes Fixed Size 15697000 bytes Variable Size 1.1878E+10 bytes Database Buffers 2717908992 bytes Redo Buffers 51404800 bytes Database mounted. ORA-00603: ORACLE server session terminated by fatal error ORA-01092: ORACLE instance terminated. Disconnection forced ORA-14695: MAX_STRING_SIZE migration is incomplete Process ID: 18669 Session ID: 401 Serial number: 16419
So start the database again in UPGRADE mode and execute utlk32.sql script.
2. If you try to set the value to EXTENDED , when database is not in UPGRADE mode.
SQL> alter system set MAX_STRING_SIZE=EXTENDED; alter system set MAX_STRING_SIZE=EXTENDED * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration
So before running this alter statement, start the database in UPGRADE mode.
CONCLUSION: Once the max_string_size is changed to
EXTENDED,We can not revert to originalSTANDARDvalue.So before changing the value in critical or production database, Please do thorough testing , As you may hit uninvited bugs.
NOTE: As suggested by one Mark in comment section:
————————————————————-
Thanks for this.
One other thing you might want to add:
If you have items in the recyclebin when you run utl32k.sql, you’ll get:
DECLARE
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
ORA-06512: at line 121
Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 – 64bit Production
With the Automatic Storage Management option
Simple fix, purge recyclebin and re-run utl32k.sql:
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue May 12 13:24:55 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 – 64bit Production
With the Automatic Storage Management option
SQL> purge dba_recyclebin;
@?/rdbms/admin/utl32k
-Mark
—————————————————-
In your last conclusion, you wanted may be to say that you CANNOT revert back to Standard, right?
I have corrected it. Thanks for pointing it out 🙂
Thanks for this.
One other thing you might want to add:
If you have items in the recyclebin when you run utl32k.sql, you’ll get:
DECLARE
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
ORA-06512: at line 121
Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 – 64bit Production
With the Automatic Storage Management option
Simple fix, purge recyclebin and re-run utl32k.sql:
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue May 12 13:24:55 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 – 64bit Production
With the Automatic Storage Management option
SQL> purge dba_recyclebin;
@?/rdbms/admin/utl32k
-Mark
Thanks Mark, updated the article
What are the disadvantages/complications downstream if you switch to extended?
In general it should not cause any issue. However few applications might not support this. i have heard of EBS causing problems with extednded size.