Oracle 12C-Extended size limits for VARCHAR2, NVARCHAR2, and raw data types

In 12C, the VARCHAR2,NVARCHAR2 and raw types extend from previous 4K to 32K

After upgrading to 12C, the parameter max_string_size default value is standard, that is, does not change the size limit of VARCHAR2, NVARCHAR2, and raw data types, and 11g is consistent.

sql> Show parameter max_string_sizename                                 TYPE        VALUE------------------------------------------- ----------------------------------max_string_size                      string      standardsql

To turn on the Extended Data type feature:

Sql> alter systemSetmax_string_size=extended scope=Both;alter SystemSetmax_string_size=extended scope=both*ERROR at line1: ORA-02097: parameter cannot be modified because specified value isInvalidora-14694: Database mustinchUPGRADE mode to begin Max_string_size Migrationsql>--Setting this parameter requires the data to start SQL in upgrade mode>shutdown immediatedatabase closed. Database dismounted. ORACLE instance shut down. SQL>Startup Upgrade;oracle instance started. Total System Global Area2483027968bytesfixed Size3713864bytesvariable Size721421496Bytesdatabase Buffers1744830464Bytesredo Buffers13062144bytesdatabase mounted. Database opened. SQL> Alter systemSetmax_string_size=extended scope=both; System altered. SQL> @ $ORACLE _home/rdbms/admin/utl32k.sql

Modify the following script to execute after the upgrade may have some objects become invalid, you need to recompile the next invalid object

sql> @ $ORACLE _home/rdbms/admin/utlrp.sql

After the upgrade, if the size of varchar2,nvarchar2 and raw is larger than 4k,oracle, it will be stored in lobs (Oracle internal maintenance is not recommended for direct user operation).

Then restart the database!

You can do a test:

int, name varchar2 (32000)); SQL> INSERT INTO v32k_t values (1, Rpad (1,31999,'x' ) )); SQLSelect from

This new feature will have some of the following effects:
(1) The creation and use of indexes are impacted (as covered in the next sections in more detail).

Users may not be able to create, use indexes correctly, or insert and update operations. This is primarily limited by the length of the Oracle's B-Tree index, and the length of the B-Tree index is limited by the size of the database block. The maximum length of an index supported by a block of 8k size is 6400 bytes. It is recommended that you create a function index using SUBSTR, or create a hash index, create a virtual column with substr, and then create an index on the virtual column.

(2) The limit of the combined length of concatenated character strings is increased.
(3) The length of the collation key returned by the Nlssort function is increased.
(4) The size of some of the attributes of the Xmlformat objects is increased.
(5) The size of some expressions in some XML functions is adjusted.

Oracle 12C-Extended size limits for VARCHAR2, NVARCHAR2, and raw data types