• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to change max_string_size value from STANDARD to EXTENDED

by admin

MAX_STRING_SIZE controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL. There are 2 possible values of this parameter:

  • MAX_STRING_SIZE = STANDARD means that the length limits for Oracle Database releases prior to Oracle Database 12c apply (for example, 4000 bytes for VARCHAR2 and NVARCHAR2, and 2000 bytes for RAW).
  • MAX_STRING_SIZE = EXTENDED means that the 32767 byte limit introduced in Oracle Database 12c applies.

You can change the value of MAX_STRING_SIZE from STANDARD to EXTENDED. However, you cannot change the value of MAX_STRING_SIZE from EXTENDED to STANDARD.

Example of how to set MAX_STRING_SIZE = EXTENDED

Note: database vault should be disabled before performing the steps.

For non-CDB:
To increase the maximum size of VARCHAR2, NVARCHAR2, and RAW columns in a non-CDB:

1. Shut down the database.
2. Restart the database in UPGRADE mode.
3. Change the setting of MAX_STRING_SIZE to EXTENDED.
4. Run the rdbms/admin/utl32k.sql script. You must be connected AS SYSDBA to run the script.

For non-CDB:

SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
alter system set MAX_STRING_SIZE=EXTENDED;
@?/rdbms/admin/utl32k.sql
SHUTDOWN IMMEDIATE;
STARTUP; 
Note: If using pfile, ensure that max_string_size=extended is added to pfile before a normal startup is attempted after executing the utl32k.sql script.

For RAC system:

alter system set cluster_database = false scope = spfile;
SHUTDOWN IMMEDIATE;         --- shutdown all the instances
STARTUP UPGRADE;
alter system set MAX_STRING_SIZE=EXTENDED;
@?/rdbms/admin/utl32k.sql
alter system set cluster_database = true scope = spfile;
SHUTDOWN IMMEDIATE;
STARTUP               --- or you can use srvctl to start the database.

For PDB:
To increase the maximum size of VARCHAR2, NVARCHAR2, and RAW columns in a PDB:

1. Shut down the PDB.
2. Restart the PDB in UPGRADE mode.
3. Change the setting of MAX_STRING_SIZE in the PDB to EXTENDED.
4. Run the rdbms/admin/utl32k.sql script in the PDB. You must be connected AS SYSDBA to run the utl32k.sql script.
5. Restart the PDB in NORMAL mode.
6. Restart the database in NORMAL mode.

Note: If you are using a cluster you first have to disable it using cluster_database=False and then start the DB in startup upgrade mode. PDB$SEED should be upgraded as well.

Filed Under: oracle

Some more articles you might also be interested in …

  1. Unable to create spfile for Oracle ASM instance
  2. DNS and DHCP Setup Example for Oracle Grid Infrastructure GNS
  3. Oracle Database 12c New Feature: SYSRAC administrative privilege
  4. How to Create a Physical Standby Database by Using SQL and RMAN Commands
  5. Unable to export realm protected table using data pump
  6. Dynamic Oracle Net Server Tracing
  7. How to Configure Device File owner/group with udev rules
  8. Oracle RMAN interview questions
  9. Script To Find Redolog Switch History And Find Archivelog Size For Each Instances In Oracle RAC
  10. How to get the Values Assigned by Default to a Profile in Oracle Database

You May Also Like

Primary Sidebar

Recent Posts

  • JavaFX ComboBox: Set a value to the combo box
  • Nginx load balancing
  • nginx 504 gateway time-out
  • Images preview with ngx_http_image_filter_module

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright