Overview
In 12c database, you can define a maximum size of 32767 bytes for VARCHAR2, NVARCHAR2 and RAW data type column.
Before 12c database, the maximum size was 4000 bytes for VARHAR2 and NVARHAR2 and 2000 bytes for RAW data type.
The declared column length for VARHAR2,NVARHAR2 and RAW data type decides how the column is stored internally in database.
- If VARCHAR2 and NVARCHAR2 are declared with column length of 4000 bytes or less, and RAW data type column is declared with length 2000 bytes or less, the data is store in-line.
- If VARCHAR2 and NVARCHAR2 are declared with column length of greater than 4000 bytes, and RAW data type column is declared with length of greater than 2000 bytes, the data is store out-of-line. The column is called “extended character data type column”.
Configure Database for Extended Data Type
- Before configuring extended data type feature for database.
$ sqlplus / as sysdba SQL*Plus:Release 12.1.0.2.0 Production on Mon Nov 17 21:54:47 2014Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options SQL> create table test_table(longvar varchar2(32767)); create table long_varchar(id number,vc varchar2(32767)) * ERROR at line 1: ORA-00910: specified length too long for its datatype
SQL> alter system set max_string_size=extended; system set max_string_size=extended * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration
- Shutdown Database
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
- Start database in upgrade mode
SQL> startup upgrade ORACLE instance started. TotalSystem Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 675283048 bytes Database Buffers 390070272 bytes Redo Buffers 5455872 bytes Database mounted. Database opened.
- Change MAX_STRING_SIZE setting
SQL> alter system set max_string_size=extended; System altered.
- Run utl32k.sql script
SQL> @ $ORACLE_HOME/rdbms/admin/utl32k.sql Session altered. DOC>############################################################## DOC>############################################################## DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error if the database has not been opened for UPGRADE. DOC> DOC> Perform a "SHUTDOWN ABORT" and DOC> restart using UPGRADE. DOC>############################################################## DOC>############################################################## DOC># no rows selected DOC>############################################################## DOC>############################################################## DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error if the database does not have compatible >= 12.0.0 DOC> DOC> Set compatible >= 12.0.0 and retry. DOC>############################################################## DOC>##############################################################DOC># PL/SQL procedure successfully completed. Session altered. 1393 rows updated. Commit complete. System altered. PL/SQL procedure successfully completed. Commit complete. System altered. Session altered. PL/SQL procedure successfully completed. No errors. Session altered. PL/SQL procedure successfully completed. Commit complete. Package altered. Package altered. SQL>
- Shutdown and Startup database
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 675283048 bytes Database Buffers 390070272 bytes Redo Buffers 5455872 bytes Database mounted. Database opened.
- Check parameter setting and create table with extended data type column
SQL> show parameter max_string
NAME TYPE VALUE ---------------- ------- -------- max_string_size string EXTENDED SQL> create table test_table(longvar varchar2(32767)); Table created.
Please note you can not change parameter MAX_STRING_SIZE from EXTENDED to STANDARD.
Make DBA Life Easy 