Something Between VARCHAR2 and CLOB: VARCHAR2 on Steroids [MAX_STRING

About five years ago, while developing a CRM module, I needed to implement a field where the salesperson could describe the entire conversation they had with a customer.

Initially, I went with the obvious approach and created a VARCHAR2(4000) column. After some time using it, one of the salespeople surprisingly started complaining that this limit was too small for the level of detail she wanted to include in her notes. She asked me to increase the limit.

In Oracle, we have a limitation of 4000 characters for VARCHAR2, and the typical upgrade path would be to switch to a CLOB. However, that would involve changing the existing screen and modifying a few things at the session and integration levels. Switching to a CLOB also changes how the database stores these records and how multiple applications access those columns.

I decided to research a bit, and soon I came across a solution that has actually existed since Oracle 12c — a simple parameter called MAX_STRING_SIZE.

If this parameter is changed from STANDARD to EXTENDED, you can work with VARCHAR2(32000) — more precisely, VARCHAR2(32767). It doesn’t give you the same capacity as a CLOB, but it’s a great upgrade from VARCHAR2(4000) and perfect for cases where you need something in between.

Before making any changes, I recommend reading this official Oracle documentation to determine whether this is the right choice for your environment.

To check whether your database already has this parameter enabled, run the query below (assuming you have permission to access v$parameter):

SELECT name, value 
FROM v$parameter 
WHERE name = 'max_string_size';

Here, I’ll show how to modify it in Autonomous Database as an example, but in the link above you’ll also find how to apply it in a standard CDB, PDB, and other configurations.

When logged in as ADMIN, execute:

 BEGIN
     DBMS_MAX_STRING_SIZE.MODIFY_MAX_STRING_SIZE('EXTENDED');
   END;

That’s it. Problem solved. From now on, it’s smooth sailing. 😎

A few important notes

The change is irreversible — once you set it to EXTENDED, you cannot revert to STANDARD without recreating the database.
It may affect compatibility with older clients (SQL Developer, JDBC drivers, etc.).
It can also impact backups or replications if you have legacy systems involved.

I had never thought about writing a post about this before because I assumed the parameter was already well known. However, over the past year, I’ve realized that many people I’ve talked to — including some DBAs — were not aware of it, so I decided it was worth sharing.

💬 What about you? Had you heard about this before? Have you used it? Did you find it useful?

References:

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/MAX_STRING_SIZE.html

https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/dbms-max-string-size.html