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

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

Error: On creating a user getting the following error:

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

Cause: Check the value of the parameter

SQL>  show parameter max_string

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

Solution: change the max_string_size to Extended


-- Change the parameter in spfile
SQL> alter system set max_string_size=extended scope=spfile;

System altered.

-- For make effect restart the DB
SQL> shutdown immediate
Pluggable Database closed.

--But getting error in restart
SQL> startup
ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration

--Start databases in upgrade state and change to extended state with utl32k.sql
SQL> startup upgrade
Pluggable Database opened.
SQL> @?/rdbms/admin/utl32k.sql;

Run the ut123k.sql for convert:


SQL> @?/rdbms/admin/utl32k.sql;

Session altered.


Session altered.

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database has not been opened for UPGRADE.
DOC>
DOC>   Perform a "SHUTDOWN ABORT"  and
DOC>   restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database does not have compatible >= 12.0.0
DOC>
DOC>   Set compatible >= 12.0.0 and retry.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

PL/SQL procedure successfully completed.

Session altered.

0 rows updated.

Commit complete.

System altered.

PL/SQL procedure successfully completed.

Commit complete.

System altered.

Session altered.

Session altered.

Table created.

Table created.

Table created.

Table truncated.

0 rows created.

PL/SQL procedure successfully completed.

STARTTIME
--------------------------------------------------------------------------------
02/11/2022 18:32:32.897000000

PL/SQL procedure successfully completed.

No errors.

PL/SQL procedure successfully completed.

Session altered.

Session altered.

0 rows created.

no rows selected

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if we encountered an error while modifying a column to
DOC>   account for data type length change as a result of enabling or
DOC>   disabling 32k types.
DOC>
DOC>   Contact Oracle support for assistance.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Package altered.

Session altered.

Shutdown the database and start in normal mode:

SQL> shutdown immediate
Pluggable Database closed.

SQL> startup
Pluggable Database opened.

Verify the changes in the Database:

SQL> show parameter max_string_size

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


SQL> CREATE USER hruser IDENTIFIED BY hrpassword DEFAULT COLLATION BINARY_CI;

User created.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.