Changing MAX_STRING_SIZE - Standard to Extended

 

The parameter MAX_STRING_SIZE controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL.

 

Setting the parameter as,

•       MAX_STRING_SIZE = STANDARD means that the length limits for Oracle Database releases prior to Oracle Database 12c apply (for example, 4000 bytes for VARCHAR2 and NVARCHAR2, and 2000 bytes for RAW).

•       MAX_STRING_SIZE = EXTENDED means that the 32767-byte limit introduced in Oracle Database 12c applies.

 

We can change the value of MAX_STRING_SIZE from STANDARD to EXTENDED. However, you cannot change the value of MAX_STRING_SIZE from EXTENDED to STANDARD.

 

Additionally, there are no options to rollback this change. The only way to revert is to restore the database from backup.

 

Steps to Implement :-

Backup Current state of database. As there is no rollback after changing the parameter.
SQL > Shutdown immediate
SQL > startup upgrade
SQL > alter system set MAX_STRING_SIZE=EXTENDED;

SQL> @?/rdbms/admin/utl32k.sql
Session altered.
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.
333 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.
Session altered.
PL/SQL procedure successfully completed.
STARTTIME
--------------------------------------------------------------------------------
09/16/2023 19:29:03.942000000
PL/SQL procedure successfully completed.
No errors.
Session altered.
Session altered.
Session altered.
0 rows created.
no rows selected
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.
Package altered.
Session altered.

SQL > SHUTDOWN IMMEDIATE;
SQL > STARTUP;
SQL > @?/rdbms/admin/utlrp.sql
SQL > select count(*) from dba_invalid_objects;
      COUNT(*)
      ----------
                0