Day: November 17, 2014

How to Configure 32K VARHAR2 Data Type in 12c

Configure 32k VARCHAR2, NVARCHAR2 and RAW data type by setting max_string_size=extended, and be aware this parameter can no long be able to change back, like setting max_string_size=standard.

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.

Advertisement