There are several scenarios to think about Non CDB, PDB, CDB+PDB
This change can’t be backed out once implemented
Scenario 1. changing on a non CDB
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/MAX_STRING_SIZE.html
sqlplus / as sysdba
select name, cdb from v$database;
shutdown immediate;
startup upgrade;
show parameter max_string_size scope=spfile;
alter system set max_string_size=extended scope=spfile;
— as sys user
@?/rdbms/admin/utl32k.sql
shutdown immediate
startup
select con_id, name, value from V$SYSTEM_PARAMETER where name=’max_string_size’;
@?/rdbms/admin/utlrp
Scenario 2. Scenario 2 PDB without changing on the CDB$ROOT
see: https://mikedietrichde.com/2021/01/15/cool-features-web-seminar-jan-2021-quick-recap/
(The parameter is PDB_MODIFIABLE. And you can enable it in a PDB without enabling it in the CDB$ROOT before)
sqlplus / as sysdba
show pdbs
alter session set container=PDB; — my pdb is called PDB
select name, open_mode from v$pdbs;
— if needed start pdb
startup
show con_name
show parameter max_string_size
alter system set max_string_size=extended scope=spfile;
shutdown
startup upgrade
@?/rdbms/admin/utl32k.sql
shutdown
startup
@?/rdbms/admin/utlrp
show parameter max_string_size
alter session set container=CDB$ROOT;
select con_id, name, value from V$SYSTEM_PARAMETER where name=’max_string_size’;
show parameter max_string_size
Scenario 3. CDB$ROOT and all PDBs
It maybe easier to perform on CDB, PDB separately as below using perl and catcon.pl
below commands must be run in Windows powershell
sqlplus / as sysdba
ALTER SESSION SET CONTAINER=CDB$ROOT;
ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;
shutdown immediate;
startup upgrade;
exit;
(Update Oracle home path to perl/bin in below command as needed)
$env:PATH += “;F:\WINDOWS.X64_193000_db_home\perl\bin”
perl -v
*Update ORACLE_HOME as needed for below parameter settings
$ORACLE_HOME=”F:\WINDOWS.X64_193000_db_home”
$env:ORACLE_HOME=”F:\WINDOWS.X64_193000_db_home”
cd $ORACLE_HOME # update with oracle home if different
mkdir /scratch/mydir/utl32k_cdb_pdbs_output
you will be prompted for sys password when running below
perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS –force_pdb_mode ‘UPGRADE’ -d $ORACLE_HOME/rdbms/admin -l ‘/scratch/mydir/utl32k_cdb_pdbs_output’ -b utl32k_cdb_pdbs_output utl32k.sql
review logs in : scratch\mydir\utl32k_cdb_pdbs_output
shutdown immediate
startup
exit;
cd $ORACLE_HOME/rdbms/admin
mkdir /scratch/mydir/utlrp_cdb_pdbs_output
you will be prompted for sys password when running below
perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS –force_pdb_mode ‘READ WRITE’ -d $ORACLE_HOME/rdbms/admin -l ‘/scratch/mydir/utlrp_cdb_pdbs_output’ -b utlrp_cdb_pdbs_output utlrp.sql
sqlplus / as sysdba
select con_id, name, value from V$SYSTEM_PARAMETER where name=’max_string_size’;