Extended Data Type Oracle Database 12C release 1(12.1)


New higher limits (in bytes) are available for following types

  • VARCHAR2 – 32767 bytes
  • NVARCHAR2 – 32767 bytes
  • RAW – 32767 bytes

Previous limits prior to Oracle Database 12c

  • VARCHAR2 – 4000 bytes
  • NVARCHAR2 – 4000 bytes
  • RAW – 2000 bytes

NOTE – remember new limits are always 32767 bytes even if you are using CHAR semantic


To use extended version you need to set new initialization parameter MAX_STRING_SIZE plus run script utl32k.sql on database level. COMPATIBLE must be set to and higher.

  • MAX_STRING_SIZE=STANDARD – default settings. Size limits for releases prior to Oracle Database 12c apply.
  • MAX_STRING_SIZE=EXTENDED – new size limit is 32767 bytes.

Upgrade from STANDARD to EXTENDED is one way ticket. There is no method to return back to STANDARD.

1. Change parameter and restart main database in upgrade mode (non container or container database(CDB$ROOT))

alter system set max_string_size=extended scope=spfile;
shutdown immediate;
startup upgrade;

2. Run update script (non container or container database(CDB$ROOT))


2a. In case your database is container you need to upgrade all pluggable databases as well (PDB$SEED + others)

Following query lists pluggable databases to upgrade. It’s just example.

select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
     2     PDB$SEED                       MIGRATE
     3     PORA12C1                       MOUNTED

Open each pluggable database in upgrade mode. PDB$SEED should be already in MIGRATE mode.

alter pluggable database PORA12C1 open upgrade;

select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
     2     PDB$SEED                       MIGRATE
     3     PORA12C1                       MIGRATE

Upgrade each pluggable database

alter session set container=PDB$SEED;

alter session set container=PORA12C1; 

Return back to main container database

alter session set container=CDB$ROOT;

3. Restart main database (non container or container database(CDB$ROOT))

shutdown immediate;

Columns which are using extended types are stored in tablespaces as LOBs

  • For Automatic Segment Space Management (ASSM) are stored as SecureFiles LOBs
  • Otherwise, they are stored as BasicFiles LOBs


Important database properties

select property_name, property_value 
from database_properties
where property_name in

----------------------- ---------------

NVARCHAR2 is always created with semantic CHAR and uses NLS_NCHAR_CHARACTERSET character page.

For character page NLS_NCHAR_CHARACTERSET=AL16UTF16 each character consumes 2 bytes so you can declare only 16383 characters for NVARCHAR2 because of limit 32767 bytes.

create table test_tbl
  col_var  varchar2(32767),
  col_nvar nvarchar2(16383),
  col_raw  raw(32767)

select table_name, column_name, data_type, data_length 
from user_tab_columns
where table_name='TEST_TBL';

----------  ----------- ---------  -----------
TEST_TBL    COL_VAR     VARCHAR2   32767
TEST_TBL    COL_VAR     RAW        32767

insert into test_tbl
(rpad('a', 32767, ' '), 
 rpad('a', 16383, ' '), 
 utl_raw.cast_to_raw(rpad('1', 32767, ' ')));

select 'VARCHAR2' col_type, length(col_var), lengthb(col_var) 
from test_tbl union all
select 'NVARCHAR2', length(col_nvar), lengthb(col_nvar) 
from test_tbl union all
select 'RAW', length(col_raw), lengthb(col_raw) 
from test_tbl;

--------- --------------- ----------------
VARCHAR2            32767            32767 
NVARCHAR2           16383            32766 
RAW                 32767            32767

it’s new feature and has many things and limitations to consider like:

  • Index on an extended data type column has got lower limitation in its size so tricks should be used with virtual column and STANDARD_HASH function
ALTER TABLE table ADD (new_hash_column AS (STANDARD_HASH(column)));
CREATE INDEX index ON table (new_hash_column)
  • If the partitioning key column for a list partition is an extended data type column, then the list of values that you want to specify for a partition may exceed the 4K byte limit for the partition bounds.
  • The value of the initialization parameter MAX_STRING_SIZE affects the following:
    • The maximum length of a text literal.
    • The size limit for concatenating two character strings.
    • The length of the collation key returned by the NLSSORT function.
    • The size of some of the attributes of the XMLFormat object.
    • The size of some expressions in the following XML functions: XMLCOLATTVAL, XMLELEMENT, XMLFOREST, XMLPI, and XMLTABLE.

Have a fun 🙂

3 thoughts on “Extended Data Type Oracle Database 12C release 1(12.1)

  1. Dear Friend…

    By this post I get solved the problems with MAX_STRING_SIZE on oracle 12c.


    One Brazilian man.

  2. Hi Expert ,

    can i ask one question about.

    if we migrate to datatype MAX_STRING_SIZE=EXTEND is there any drawbacks,
    related to performance and execution time sqls and already existing objects on database.

    After change if export schema into anther lower level(like 11g and 10g) due size as i think some issues will appears what is the best practice.
    if have any documents kindly share


Leave a Reply

Your email address will not be published. Required fields are marked *