In pre-12c database releases, VARCHAR2, NVARCHAR2 data types were limited to 4000 bytes and RAW data type was limited to 2000 bytes. If there is a need to store more data in VARCHAR2 column, you can in 12c after setting the parameter MAX_STRING_SIZE to EXTENDED. With setting MAX_STRING_SIZE to EXTENDED, you can store up to 32KB (32767 bytes) in VARCHAR2, NVARCHAR2 and RAW columns. Though it is an initialization parameter, there are few restrictions and requirements around the parameter setting.
The default for MAX_STRING_SIZE is STANDARD in Oracle Database 12c, where the maximum size for VARCHAR2, NVARCHAR2 and RAW are same as previous Oracle releases. To change value of this parameter to extended, you have to follow certain steps, and once you set to EXTENDED, there is no going back to STANDARD. To increase the maximum size, do:
- Shutdown the database
- Start database in UPGRADE mode
- Use ALTER SYSTEM to change MAX_STRING_SIZE to EXTENDED
- Run script $ORACLE_HOME/rdbms/admin/utl32k.sql connected as SYSDBA
- Shutdown database
- Start database in NORMAL mode
- Recompile invalid objects using $ORACLE_HOME/rdbms/admin/utlrp.sql connected as SYSDBA
There are several overheads associated with enabling increased maximum size for columns, including how such columns are stored. So, make this change only if absolutely necessary, after careful testing. The feature is introduced to help migrating data from non-Oracle databases to Oracle, where the non-Oracle databases have varchar maximum size well over 4K.