Changing MAX_STRING_SIZE in a PDB (What Worked for Me)
I recently had to deal with a requirement where the
application team wanted to store larger strings in the database. The usual
4000-byte limit wasn’t enough, so the only option was to switch MAX_STRING_SIZE
to EXTENDED.
At first I thought it would be a quick change… but in a PDB
setup, it’s not that straightforward. I actually had to redo a part of it
because I missed the seed step
So sharing the exact steps that worked for me.
Step 1: Check Current Setting
First thing I checked was:
|
SQL> show
parameter string_size NAME TYPE
VALUE ------------------------------------
----------- ------------------------------ max_string_size
string STANDARD |
It was STANDARD, which is expected in most environments.
Step 2: Close and Open PDB in Upgrade Mode
Then I closed the PDB:
SQL> ALTER PLUGGABLE DATABASE PROD CLOSE;
and opened it in upgrade mode:
SQL> ALTER PLUGGABLE DATABASE PROD OPEN UPGRADE;
Step 3: Change MAX_STRING_SIZE
Now set the parameter:
SQL>ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED SCOPE=spfile;
This doesn’t take effect immediately, so a restart is
needed.
Step 4: Restart Database in Upgrade Mode
So I restarted the CDB:
SQL> shu immediate;
SQL> startup upgrade;
Step 5: Run utl32k.sql
After startup, I ran:
SQL>@?/rdbms/admin/utl32k.sql
This is one of those steps you really shouldn’t skip.
Step 6: Close PDB and Handle PDB$SEED
Next, I closed the PDB:
SQL> ALTER PLUGGABLE DATABASE PROD CLOSE IMMEDIATE;
Then handled the seed database (this is where I messed up
the first time):
SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN UPGRADE;
SQL> ALTER SESSION SET CONTAINER = PDB$SEED;
SQL> ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
SQL> ALTER PLUGGABLE DATABASE OPEN READ ONLY;
SQL> ALTER PLUGGABLE DATABASE SAVE STATE;
If you skip this, new PDBs may not inherit the setting
properly.
Step 7: Run Script Again in Target PDB
Finally, I switched back to my PDB:
SQL> ALTER SESSION SET CONTAINER=PROD;
SQL> @?/rdbms/admin/utl32k.sql
Final Thoughts
A few things I’d keep in mind:
- Do
this during a maintenance window
- Take
a backup before starting
- Don’t
ignore the seed step
- This
change is not easily reversible
Once done, you can go beyond the
4000-byte limit — up to 32767 for VARCHAR2, which is what we needed.
