ALTER SYSTEM SET max_string_size=extended ORA-02097: parameter cannot be modified because specified value is invalid

MAX_STRING_SIZE PARAMETER:

The extended data types functionality is controlled using the MAX_STRING_SIZE initialization parameter. The default value is STANDARD, which restricts the maximum sizes to the traditional lengths. Setting the parameter value to EXTENDED allows for the new maximum lengths.

This is a pre-requisite for enabling Extended Data Types in Oracle Database 12c. Which helps us to increase the max size of varchar data type from 4000 bytes to 32767 bytes.

[oracle@Linux03 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 21 09:10:02 2016

Copyright (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 and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP UPGRADE;
ALTER SYSTEM SET max_string_size=extended;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.
Database opened.
SQL> ALTER SYSTEM SET max_string_size=extended
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified


SQL> ALTER SYSTEM SET max_string_size=extended;
ALTER SYSTEM SET max_string_size=extended
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified


SQL> show parameter max_string_size

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size     string STANDARD

SQL> alter system set max_string_size=EXTENDED scope=spfile;

System altered.

SQL> show parameter max_string_size

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size     string STANDARD
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.
Database opened.

SQL> show parameter max_string_size

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size     string EXTENDED