1

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.

1
  • Strange indeed. Can you try to connect with any user and try the following statement? ALTER SESSION SET DEFAULT_COLLATION=BINARY_CI; Sep 26, 2017 at 16:25

1 Answer 1

2

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.
5
  • I had not altered the session to the PDB before attempting to create the user.
    – Dave G
    Sep 26, 2017 at 19:54
  • Additionally - the user must connect to the PDB not the CDB. I also had to leverage oracleinaction.com/12c-use_sid_as_service_listener_name to connect to the CDB I was after.
    – Dave G
    Sep 27, 2017 at 13:41
  • @Dave G The proper way is to use SERVICE_NAME on the client side instead of SID. SID should have been forgotten years ago, it has no real use for clients while services offer multiple benefits. Sep 27, 2017 at 14:31
  • I'll give that a shot in a little bit - should I "unset" SID and set "SERVICE_NAME"? Also, does this have to be registered in the (forgive me) tnsnames.ora?
    – Dave G
    Sep 27, 2017 at 14:35
  • Ok - was able to work this out - thank you for your assistance!
    – Dave G
    Sep 27, 2017 at 20:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.