How to Change max_string_size from STANDARD to EXTENDED in Oracle 19c

Oracle Database 19c supports increasing the maximum size of VARCHAR2, NVARCHAR2, and RAW columns from 4,000 bytes (STANDARD) to 32,767 bytes (EXTENDED).
This change requires modifying the max_string_size initialization parameter and running upgrade scripts across the CDB and all PDBs.

This guide walks through the complete procedure.

1. Validate the Current Parameter Value

$ sqlplus / as sysdba
SQL> show parameter MAX_STRING_SIZE;

2. Take a PFILE Backup

$ mkdir /home/oracle/PFILE
$ sqlplus / as sysdba
SQL> create pfile='/home/oracle/initMYCDB_PROD.ora' from spfile;

3. Change `max_string_size` to EXTENDED

$ sqlplus / as sysdba
SQL> alter system set max_string_size=EXTENDED scope=spfile sid='*';

4. Shut Down the Database

$ srvctl config
$ srvctl status database -d MYCDB_PROD
$ srvctl stop database -d MYCDB_PROD

5. Start the Database in UPGRADE Mode

$ sqlplus / as sysdba
SQL> startup upgrade;

6. Open All PDBs in UPGRADE Mode

SQL> ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
SQL> EXIT;

7. Run the `utl32k.sql` Script Using `catcon.pl`

$ cd $ORACLE_HOME/rdbms/admin
$ mkdir /home/oracle/utl32k_cdb_pdbs_output

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \
  -u SYS \
  -d $ORACLE_HOME/rdbms/admin \
  -l '/home/oracle/utl32k_cdb_pdbs_output' \
  -b utl32k_cdb_pdbs_output \
  utl32k.sql

8. Restart the Database

$ sqlplus / as sysdba
SQL> shut immediate;
SQL> startup;

9. Open All PDBs in READ WRITE Mode

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;
SQL> show pdbs;

10. Recompile Invalid Objects Using `utlrp.sql`

$ cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \
  -u SYS \
  -d $ORACLE_HOME/rdbms/admin \
  -l '/home/oracle/utl32k_cdb_pdbs_output' \
  -b utlrp_cdb_pdbs_output \
  utlrp.sql

11. Shut Down and Restart with SRVCTL

$ sqlplus / as sysdba
SQL> shut immediate;

$ srvctl start database -d MYCDB_PROD

12. Validate the Updated Parameter

$ sqlplus / as sysdba
SQL> show parameter MAX_STRING_SIZE;

Summary

After switching max_string_size to EXTENDED and running the necessary upgrade scripts across the CDB and all PDBs, your Oracle 19c environment will support expanded 32K string sizes for VARCHAR2, NVARCHAR2, and RAW datatypes.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

How to Change max_string_size from STANDARD to EXTENDED in Oracle 19c