Extended Data Types Oracle 12c

Till Oracle 11g character data types like varchar2, nvarchar2 supports only 4000 bytes where as RAW supports only 2000 bytes. From Oracle 12c, Oracle has extended these datatypes length to 32Kbytes (32767 bytes). For the extended datatype support we need to configure it.

Enabling the extended datatype support on non-cdb Oracle 12c database.

Connect to non cdb database. In my case orcl is non cdb database.

. oraenv
sqlplus / as sysdba

Shutdown database and startup on upgrade mode.

shutdown immediate
startup upgrade

Modify system parameter max_string_size to extended.

alter system set max_string_size=extended;

Execute the sql file that is located under rdbms/admin directory.

@?/rdbms/admin/utl32k.sql

After completing the execution of above sql command startup database in normal mode.

shutdown immediate
startup

Enabling the extended datatype support on cdb Oracle 12c database. In my case cdb1 is cdb database with 3 pluggable database: pdb1, pdb2, pdb3.

On root container database execute the following commands

. oraenv
sqlplus / as sysdba

Modify system parameter max_string_size to extended.

alter system set max_string_size=extended scope=spfile;

Shutdown database and startup on upgrade mode.

shutdown immediate
startup upgrade

Open all pluggable database on upgrade mode.

alter pluggable database all open upgrade;

Execute utl32k.sql for all the pdbs.

Change directory to script file directory i.e. ORACLE_HOME then rdbms/admin. We are using catcon.pl, it can run sql in the root and in specified PDBs in the correct order, and it generates log files that you can view to confirm that the SQL script or SQL statement did not generate unexpected errors. It also starts multiple processes and assigns new scripts to them as they finish running scripts previously assigned to them. 

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

Login to the cdb, shutdown the database and start database in the normal mode.

sqlplus / as sysdba
shutdown immediate
startup;
alter pluggable database all open;

Using extended datatypes

Create sequence seq_id;

Create table that use extended datatype.

Create table tbl_memo (
id number default seq_id.nextval,
notes varchar2(32767),
enabled char default 'Y',
primary key(id));

Creating table on non cdb database.

Creating table on cdb root database

It is noticed that we cannot enable extended datatype at cdb root database. Even I have executed "@?/rdbms/admin/utl32k.sql" at cdb root database but there is no luck. Following error has encountered.

ORA-00910: specified length too long for its datatype

Whereas on the pdb database, we can create tables with extended datatypes.