Changing max_string_size to extended for Oracle Pluggable database

Recently we have been reported   "ORA-00910:specified length too long for its datatype"   by application team .    Upon checking we planned to upgrade MAX_STRING_SIZE to EXTENDED.

 

We cant set  only at pdb level  too   and changes are replicated to  standby through redo logs if changes are  done only at PDB .

We will be  changing  MAX_STRING_SIZE only at pdb level 

In order to expand the maximum value of varchar2 (also nvarchar2 and raw) datatypes in Oracle 12c and beyond, the max_string_size parameter needs to be set to "extended".  This will change the maximum value from 4k (4096) to 32k (32767).

WARNING:  You MUST run utl32k.sql immediately after changing max_string_size=extended, else you risk invalidating the database columns.

Notes  : 

1) The COMPATIBLE initialization parameter must be set to 12.0.0.0 or higher to set MAX_STRING_SIZE = EXTENDED.

2) The utl32k.sql script increases the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns for the views where this is required. The script does not increase the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns in some views because of the way the SQL for those views is written.

3) When the 32k varchar2 feature is enabled, then any function based indexes that rely on PL/SQL functions returning a varchar2 will become unusable when the utl32k.sql script is run. Such indexes will subsequently have to be dropped and it will not be possible to re-create them. Any attempt to recreate such an index will fail with ORA-1450. The reason for this is that such functional indexes will have a "key" size of 32k and this is larger than the maximum allowed key size. Prior to enabling the 32k varchar2 feature, such indexes would have had a key size of 4000 bytes, which is within the allowed limit for an index key.

4) In some cases

Running the rdbms/admin/utl32k.sql script may cause the below error:

a-ORA-14415: index in partially dropped state, submit DROP INDEX

This is caused by  BUG 21450985 - ORA-14415: INDEX IN PARTIALLY DROPPED STATE, SUBMIT DROP INDEXSev 1 24X7 SR

b-ORA-30556: either functional or bitmap join index is defined on the column to be modified

This is caused by Bug 20539050 - ORA-30556 ON USING /RDBMS/ADMIN/UTL32K

Both the bugs are fixed in 12.2 version.For earlier versions one off patch can be requested by creating a SR to Oracle Support.

c-ORA-02019 WHEN RUNNING @?/RDBMS/ADMIN/UTL32K.SQL ON mview with private DB link

This is caused by BUG 19063812 - ORA-02019 WHEN RUNNING @?/RDBMS/ADMIN/UTL32K.SQL ON MV WITH PRIVATE DB LINK

It is fixed in 12.2.

Steps : 

1)  Purge  recyclebin in pdb 

1) Take pre snap  of invalid Objects in pdb 

2) Shut down the PDB abd restart  in upgrade mode 

alter pluggable database PDB1 close immediate instances=all ;

ALTER PLUGGABLE DATABASE pdb-name OPEN UPGRADE;

3)  Change the setting of MAX_STRING_SIZE in the PDB to EXTENDED.

alter system set MAX_STRING_SIZE=EXTENDED  sid='*';

@?/rdbms/admin/utl32k.sql

 Note:

The utl32k.sql script increases the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns for the views where this is required. 

The script does not increase the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns in some views because of the way the SQL for those views is written.

Run the rdbms/admin/utlrp.sql script in the PDB to recompile invalid objects. You must be connected AS SYSDBA to run the script.

4) Reopen the PDB in NORMAL mode

alter pluggable database PDB1 close immediate instances=all ;

ALTER PLUGGABLE DATABASE pdb-name OPEN  ;

Verify client connectivity using service .

5)  Compile Invalid Objects .

Generate  post snap of invalid objects   and  compare with pre snap taken   in step 1 

6)  Replicating to  Standby  :

Since we are  changing at PDB level   ,   changes should replicate to standby   through archive logs . 

Try to open in mount state temporarily and then normalize  in   read only mode after sometime . 

Views : 

select NAME,VALUE,ISPDB_MODIFIABLE,CON_ID from v$system_parameter where upper(NAME)='MAX_STRING_SIZE';

Reference : 

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

Effect of Max_String_Size on Data guard (Doc ID 2010497.1)

How to Increase the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in 12C Database using MAX_STRING_SIZE ? (Doc ID 1570297.1)

Alter Pluggable Database Close Instances=ALL Not Working (Doc ID 2062080.1)