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
- 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.
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