Starting Oracle Database version 12.1, you can have your VARCHAR2 [and NVARCHAR2, RAW] column width up to 32767 bytes or characters.
To be able to use VARCHAR2 columns with more that 4000 bytes or characters [pre-12c maximum], you will need to set the initialization parameter MAX_STRING_SIZE = EXTENDED. By default the value of this parameter is STANDARD, which allows only 4000 bytes or characters for VARCHAR2.
MAX_STRING_SIZE controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL. 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).
EXTENDED means that the 32767 byte limit introduced in the Oracle Database 12c release applies.The COMPATIBLE initialization parameter must be set to 12.0.0.0 or higher to set MAX_STRING_SIZE = EXTENDED.
You can change the value of MAX_STRING_SIZE from STANDARD to EXTENDED. Once changed, you cannot change the value of MAX_STRING_SIZE from EXTENDED to STANDARD.
Changing the parameter value invalidates database objects such as views and materialized views. So, be sure to compile the objects using utlrp after the change.
To change the MAX_STRING_SIZE from STANDARD to EXTENDED, you must perform the following:
- Shutdown database or PDB
- Start the database or PDB in UPGRADE mode
- Change the setting of MAX_STRING_SIZE to EXTENDED
- Run the ?/rdbms/admin/utl32k.sql script as SYSDBA.
- Restart the database or PDB in normal mode.
Note: for RAC databases all nodes must be restarted after the change.