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 cannot revert to original STANDARD value.So before changing the value in critical or production database, Please do thorough testing , As you may hit uninvited bugs.
