In Oracle Database 12c, one of the changes is to increase the maximum size of VARCHAR2, also including NVARCHAR2 and RAW, in table columns to 32K. In the past, VARCHAR2(including NVARCHAR2 and RAW) in PL/SQL code can be in size of 32K. However, for table columns, the limitation is 4000.
Does the 32K VARCHAR2 feature in Oracle Database 12c have any limitations?
Yes, it not supported in clustered and index organized tables
The followings is an example. You can't use the feature without some additional setup steps.
Does the 32K VARCHAR2 feature in Oracle Database 12c have any limitations?
Yes, it not supported in clustered and index organized tables
The followings is an example. You can't use the feature without some additional setup steps.
SQL> create table foo(bar varchar2(32766)); create table foo(bar varchar2(32766)) * ERROR at line 1: ORA-00910: specified length too long for its datatype
The reason is that you need to set the MAX_STRING_SIZE parameter to be EXTENDED instead of STANDARD (default), which has two options EXTENDED and STANDARD in Oracle Database 12c.
However, there are some impacts (refer to Oracle documentation) on this operation and you can't change MAX_STRING_SIZE from EXTENDED to STANDARD. This means you can't change it back!
Let's do a simple exercise shown as follows by logging into the CDB first and work on updating the max_string_size for PDB seed_copy.
- STANDARD: means RAW, VARCHAR2, NVARCHAR2 upper limit is 4000 bytes.
- EXTENDED: means RAW, VARCHAR2, NVARCHAR2 upper limit is 32767 bytes.
However, there are some impacts (refer to Oracle documentation) on this operation and you can't change MAX_STRING_SIZE from EXTENDED to STANDARD. This means you can't change it back!
Let's do a simple exercise shown as follows by logging into the CDB first and work on updating the max_string_size for PDB seed_copy.
Why I get the "ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration" error?
Make sure you startup the PDB/CDB in the upgrade mode.
The output of running the utl32k.sql is here.
Why I have to run utl32K.sql?
The utl32k.sql script increases the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns for the views where this is required.
Note that we only change the setup in PDB (seed_copy). The setup in the CDB and other PDB are still under the STANDARD (default) setting.
Make sure you startup the PDB/CDB in the upgrade mode.
The output of running the utl32k.sql is here.
Why I have to run utl32K.sql?
The utl32k.sql script increases the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns for the views where this is required.
Note that we only change the setup in PDB (seed_copy). The setup in the CDB and other PDB are still under the STANDARD (default) setting.
SQL> alter session set container=cdb$root; Session altered. SQL> show parameter max_string_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_string_size string STANDARD