ORACLE 18c XE (18.0.0.0.0) - MAX_STRING_SIZE = EXTENDED (COLLATE BINARY_CI) problem

Question

I set MAX_STRING_SIZE from STANDARD to EXTENDED with this command:

ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED SCOPE=spfile;

This query is finished with "Done". I checked it with this command:

SELECT * FROM v$parameter WHERE name = 'max_string_size';

..and here is the result in multirows format:

NAME                    max_string_size
VALUE                   EXTENDED
DISPLAY_VALUE           EXTENDED
DEFAULT_VALUE           STANDARD
ISDEFAULT               FALSE
ISSES_MODIFIABLE        FALSE
ISSYS_MODIFIABLE        IMMEDIATE
ISPDB_MODIFIABLE        TRUE
ISINSTANCE_MODIFIABLE   FALSE
ISMODIFIED              FALSE
ISADJUSTED              FALSE
ISDEPRECATED            FALSE
ISBASIC                 FALSE
DESCRIPTION             controls maximum size of VARCHAR2, NVARCHAR2, and RAW types in SQL

I would like to use COLLATE BINARY_CI in a CREATE TABLE and I have the following error message:

CREATE TABLE sample_db 
  (
       sample_column VARCHAR2(50) COLLATE BINARY_CI
  )

ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set

Oracle Version is: ORACLE 18c XE (18.0.0.0.0)

Answer 1

Solved.

The problem was I tried to modify value of MAX_STRING_SIZE parameter in CDB instead of PDB.

Modify commands with 'XEPDB1':

PURGE DBA_RECYCLEBIN;
ALTER PLUGGABLE DATABASE XEPDB1 CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE XEPDB1 OPEN UPGRADE;
ALTER SESSION SET CONTAINER=XEPDB1;
ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED;
@?/rdbms/admin/utl32k.sql;
ALTER PLUGGABLE DATABASE XEPDB1 CLOSE;
ALTER PLUGGABLE DATABASE XEPDB1 OPEN;

Answer 2

It is not quite as simple as ALTER ...; See Documentation:

Use ALTER SYSTEM only when the database is in UPGRADE mode, and run the utl32k.sql script afterward, as explained in this section.
...
The COMPATIBLE initialization parameter must be set to 12.0.0.0 or higher to set MAX_STRING_SIZE = EXTENDED.

Answer 3

You can follow oracle documentation: https://docs.oracle.com/database/121/REFRN/GUID-D424D23B-0933-425F-BC69-9C0E6724693C.htm#REFRN10321 at section 'Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in a PDB'.

Commands run with sysdba user:

# list pdbs
show pdbs;

# alter session to a specific pdb
alter session set container = {pdb};

# restart pdb in upgrade mode
shutdown;
alter pluggable dabase {pdb} open upgrade;

# change max_string_size
alter system set max_string_size=extended;

# run oracle script
@${ORACLE_HOME}/admin/utl32k.sql;

# reopen as normal mode
shutdown;
alter pluggable database {pdb} open READ WRITE;

# recompile invalid objects
@${ORACLE_HOME}/rdbms/admin/utlrp.sql;

I used https://hub.docker.com/r/pvargacl/oracle-xe-18.4.0 container image. The page has guidelines for connectiog to sysdba mode.