Issue
- How to change max_string_size value from STANDARD to EXTENDED in Oracle 19c?
Environment
- Liferay DXP 7.2 SP3
- Oracle 19c
Resolution
Steps for converting max_string_size value to EXTENDED:
SQL> show parameter max_string_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string STANDARD
1. Start the database in upgrade mode:
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP UPGRADE
ORACLE instance started.
Total System Global Area 3221223952 bytes
Fixed Size 9271824 bytes
Variable Size 654311424 bytes
Database Buffers 2550136832 bytes
Redo Buffers 7503872 bytes
Database mounted.
Database opened.
2. Changing the value to EXTENDED:
SQL> alter system set max_string_size=EXTENDED scope=spfile;
System altered.
[If this command doesn't work try executing the same command again by changing the scope value to both. i.e. SCOPE=BOTH]
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 3221223952 bytes
Fixed Size 9271824 bytes
Variable Size 654311424 bytes
Database Buffers 2550136832 bytes
Redo Buffers 7503872 bytes
Database mounted.
Database opened.
SQL> show parameter max_string_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string EXTENDED
Additional Information
-
We can change the value of
MAX_STRING_SIZEfromSTANDARDtoEXTENDEDbut vice-versa is not possible so changing the value is totally at your discretion.