Introduction

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

Configuration

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 12.0.0.0 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))

@?/rdbms/admin/utl32k.sql

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;
@?/rdbms/admin/utl32k.sql

alter session set container=PORA12C1; 
@?/rdbms/admin/utl32k.sql

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;
startup;

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

Examples

Important database properties

select property_name, property_value 
from database_properties
where property_name in
('NLS_NCHAR_CHARACTERSET',
 'NLS_CHARACTERSET',
 'NLS_LENGTH_SEMANTICS');

PROPERTY_NAME           PROPERTY_VALUE
----------------------- ---------------
NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_LENGTH_SEMANTICS    BYTE
NLS_CHARACTERSET        WE8MSWIN1252

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';

TABLE_NAME  COLUMN_NAME DATA_TYPE  DATA_LENGTH
----------  ----------- ---------  -----------
TEST_TBL    COL_VAR     VARCHAR2   32767
TEST_TBL    COL_NVAR    NVARCHAR2  32766
TEST_TBL    COL_VAR     RAW        32767

insert into test_tbl
values
(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;

COL_TYPE  LENGTH(COL_VAR) LENGTHB(COL_VAR)
--------- --------------- ----------------
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 🙂
Tomasz