Oracle 12c R2 – Creating User with Default Collation binary_ci Fails

collationoracleoracle-12c

If anyone can give me some guidance on this one I'd really appreciate it.

I am attempting to get a user under Oracle 12c R2 configured with default collation binary_ci.

CREATE USER <some user> IDENTIFIED BY <some password> DEFAULT COLLATION BINARY_CI;

The output I get from the above is as follows:

CREATE USER <some user>IDENTIFIED BY <some password>DEFAULT COLLATION BINARY_CI
                                     *
ERROR at line 1:
ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set.

Now, I am doing this under a Vagrant installation from https://github.com/totalamateurhour/oracle-12.2-vagrant. This essentially sets up a basic Oracle database in PDB/CDB mode. (last time I touched Oracle was 9… something so bear with me).

My DBAs have given some guidance on this but they said they were not able to get this to work in PDB mode.

Under SQL*plus execute:

CONN / AS SYSDBA
ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
EXIT;

At the command prompt:

$ cd $ORACLE_HOME/rdbms/admin/
$ $ORACLE_HOME/perl/bin/perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b /tmp/utl32k_output utl32k.sql

Then back in SQL*plus execute:

CONN / AS SYSDBA
SHUTDOWN IMMEDIATE;
STARTUP;

After executing the above and attempting to create the user as I stated earlier I still get the same error.

If I query the configuration I see the following:

SQL> show parameter max_string_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED
SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> show parameter max_string_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED
SQL>

As I said, any help would be GREATLY appreciated.

Best Answer

Works here.

SQL> alter session set container=pdb1;

Session altered.

SQL> CREATE USER a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI;
CREATE USER a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI
                            *
ERROR at line 1:
ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD
is set.


SQL> show parameter max_string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD

SQL> conn / as sysdba
Connected.

SQL> alter system set max_string_size=extended scope=spfile;

System altered.

SQL> shu immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8801008 bytes
Variable Size             306185488 bytes
Database Buffers          750780416 bytes
Redo Buffers                7974912 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open upgrade;

Pluggable database altered.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@o71 ~]$ cd $ORACLE_HOME/rdbms/admin/
[oracle@o71 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b utl32k_output utl32k.sql
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utl32k_output_catcon_6969.lst]
catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utl32k_output*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utl32k_output_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@o71 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Sep 26 20:47:57 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8801008 bytes
Variable Size             306185488 bytes
Database Buffers          750780416 bytes
Redo Buffers                7974912 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter session set container=pdb1;

Session altered.

SQL> show parameter max_string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED

SQL> CREATE USER a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI;

User created.

However, the behaviour is unchanged in the root container:

SQL> conn / as sysdba
Connected.
SQL> show parameter max_string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED

SQL> CREATE USER c##a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI;
CREATE USER c##a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI
                               *
ERROR at line 1:
ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD
is set.

The below makes it possible to overcome this, but I do not recommend it, as it is neither documented nor supported (it even allows creating a common user without the common prefix):

SQL> conn / as sysdba
Connected.

SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL> CREATE USER c##a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI;

User created.

SQL> CREATE USER a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI;

User created.
Related Question