Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in 12C Database

In Oracle Database 12c, VARCHAR2 and NVARCHAR2 data types supports size up to 32767 bytes. This article explains how to increase maximum size of VARCHAR2, NVARCHAR2 and RAW columns in oracle 12c database using MAX_STRING_SIZE parameter. Prior to 12c version, VARCHAR2 and NVARCHAR2 data types supports 4000 bytes of characters whereas RAW datatype supports up to 2000 bytes of characters. In oracle 12c, MAX_STRING_SIZE parameter controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL. It has two values

  1. MAX_STRING_SIZE = STANDARD – It means database supports 4000 bytes for VARCHAR2 and NVARCHAR2, and 2000 bytes for RAW.
  2. MAX_STRING_SIZE = EXTENDED – It means 32767 byte limit applies.

Note: We can change value of MAX_STRING_SIZE from STANDARD to EXTENDED but not vice-versa.

Below are the steps to change the value of MAX_STRING_SIZE from STANDARD to EXTENDED.

  1. Shut down the database.
  2. Restart the database in UPGRADE mode.
  3. Change the setting of MAX_STRING_SIZE to EXTENDED.
  4. Run the rdbms/admin/utl32k.sql script. You must be connected AS SYSDBA to run the script.
  5. Restart the database in NORMAL mode.

Below example shows how to change the value of MAX_STRING_SIZE from STANDARD to EXTENDED.

1. Shut down the database

SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

2.Restart the database in UPGRADE mode

SQL> STARTUP UPGRADE;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2288872 bytes
Variable Size             956302104 bytes
Database Buffers          637534208 bytes
Redo Buffers                7286784 bytes
Database mounted.
Database opened.
SQL>

3.Change the setting of MAX_STRING_SIZE to EXTENDED

SQL> ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED;

System altered.

SQL>

4. Run utl32k.sql script

SQL> @?/rdbms/admin/utl32k.sql

Session altered.
..
..--- output omited 
..
...Setting DBMS Registry Complete 12:36:58
...Exiting validate 12:36:58

PL/SQL procedure successfully completed.

SQL>

5.Restart the database in NORMAL mode.

SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2288872 bytes
Variable Size             956302104 bytes
Database Buffers          637534208 bytes
Redo Buffers                7286784 bytes
Database mounted.
Database opened.
SQL>

Now we will create a table which will have column of size 32767 bytes.

SQL>
SQL> create table table_test_varchar
  2  (
  3  column_name        varchar2(32767)
  4  );

Table created.

SQL>

Table is created. It’s working.

So, we have seen the how to increase maximum size of varchar2 in oracle 12c database.

Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in 12C Database

Leave a Reply