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 STANDARDSolution: 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.
Smart way of Technology 