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;