Configure extended data types in Oracle

Prior to Oracle 12c, regardless of the character semantics used, the maximum size of a VARCHAR2, NVARCHAR2 and RAW columns in a database are as follows.

• VARCHAR2 : 4000 bytes
• NVARCHAR2 : 4000 bytes
• RAW : 2000 bytes

With the introduction of Extended Data Types, Oracle 12c optionally increases these maximum sizes.

• VARCHAR2 : 32767 bytes
• NVARCHAR2 : 32767 bytes
• RAW : 32767 bytes

Remember, these figures are in bytes, not characters. The total number of characters that can be stored will depend on the character sets being used.

Prerequisites

An instance of Oracle 12c release 2

Connect to oracle with sqlplus

First thing first set the correct ORACLE_SID environment variable pointing to the DB that we wont to upgrade.

ORACLE_SID=[YOUR DATABASE SID]; export ORACLE_SID

Now we can connect to the DB:

sqlplus sys as sysdba and when asked insert the password. Once we are logged in lets execute the instructions to extend the default maxstringsize parameter:

These instructions will modify the maxstringsize an all pluggable databases as well.

ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
EXIT;

cd $ORACLE_HOME/rdbms/admin/
$ORACLE_HOME/perl/bin/perl catcon.pl -d $ORACLE_HOME/rdbms/admin -l /tmp -b utl32k_output utl32k.sql

sqlplus sys as sysdba
SHUTDOWN IMMEDIATE;
STARTUP;

Info

For more detailed information look at the official Oracle documentation:

https://oracle-base.com/articles/12c/extended-data-types-12cR1