Dealing with ORA-00910: Dive into Oracle’s MAX_STRING_SIZE Parameter

 Ever tried creating a table with a VARCHAR2(32767) column in Oracle and hit the dreaded ORA-00910 error? It looks something like this:

SQL> create table oradb_table(column1 varchar2(32767));
create table oradb_table(col1 varchar2(32767))
               *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

Bummer, right? Well, don’t fret! There’s a nifty feature introduced in Oracle Database 12c that can save your day – the MAX_STRING_SIZE parameter. Let's break it down and add some fun along the way.

Conclusion What's the Deal with MAX_STRING_SIZE ? 

The MAX_STRING_SIZE parameter controls the maximum size for VARCHAR2, NVARCHAR2, and RAW data types. By default, Oracle sets this to STANDARD, meaning your VARCHAR2 columns max out at 4000 bytes, NVARCHAR2 at 4000 bytes, and RAW at 2000 bytes. But what if we want more? Enter EXTENDED.

Setting MAX_STRING_SIZE to EXTENDED boosts the limit to a whopping 32767 bytes for these data types. However, there are some hoops to jump through. Let’s go through the steps.

Step-by-step guide to extending String Size

Step 1: Prepare Your Database

First things first, you need to connect to the Container Database (CDB) as SYSDBA. Then, set the MAX_STRING_SIZE to EXTENDED.

ALTER SESSION SET CONTAINER=CDB$ROOT;
ALTER SYSTEM SET max_string_size=extended container=all SCOPE=SPFILE;

Step 2: Move to Upgrade Mode

Now, close your Pluggable Databases (PDBs) if they’re open and restart the CDB in upgrade mode. Also, ensure all your PDBs are in upgrade mode and the status is MIGRATE.

SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;

SHOW PDBS;

Step 3: Run the Magic Script

Time to run the utl32k.sql script to increase the maximum size for your columns. Use the catcon.pl script to apply this across all PDBs.

You'll be prompted for the SYS password during this process. After it completes, you should see confirmation messages in your specified output directory.

$ mkdir -p /oracle/oradata1/utl32k_output
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS --force_pdb_mode 'UPGRADE' -d $ORACLE_HOME/rdbms/admin -l '/oracle/oradata1/utl32k_cdb_output' -b utl32k_output utl32k.sql
..

..

Enter Password: 

catcon.pl: completed successfully

Step 4: Back to Normal

Once the script runs successfully, bring the database back to normal mode.

SHUTDOWN IMMEDIATE;
STARTUP;
SHOW PDBS;

Step 5: Validate the invalid objects

Lastly, run utlrp.sql in all PDBs to compile any invalid objects that might exist after changing the parameter.

@$ORACLE_HOME/rdbms/admin/utlrp.sql; 

And there you have it! Your database is now ready to handle those massive VARCHAR2(32767) columns without breaking a sweat. 

Happy querying!

Cheers!