• 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. Oracle GoldenGate: Extract Sample Parameter File
  2. How to Split a Partition Into Multiple Partitions in Oracle 12c
  3. How to reclaim entire space of an oracle database table with “Truncate Table” statement
  4. What are Oracle Data Guard Protection Modes (redo transport rules) and how to configure them
  5. SQL script to find tables that are fragmented
  6. ORA-12547: TNS:lost Contact (Oracle 12c2)
  7. Oracle RMAN – Restore and Recovery of a Noarchivelog Database
  8. How to Delete ASM Disk on Multipath Device in CentOS/RHEL
  9. Oracle Tablespace Transport for a Single Partition
  10. RMAN Restore/Duplicate Performs Implicit Crosschecking and Cataloging

You May Also Like

Primary Sidebar

Recent Posts

  • cf: Command-line tool to manage apps and services on Cloud Foundry
  • certutil: Manage keys and certificates in both NSS databases and other NSS tokens
  • cdk: A CLI for AWS Cloud Development Kit (CDK)
  • cd: Change the current working directory

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright