Oracle Setting varchar2 nvarchar2 max size to 32767 (12c、18c、19c...later) (max_string_size=extended)

1. 直接調整 max_string_size，會出現 ORA-14694，資料庫需要重啟到 upgrade 模式進行設定 (if not restart to upgrade mode, it will be show error ORA-14694)

SQL> show parameter max_string_size;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
max_string_size 		     string	 STANDARD
SQL> alter system set max_string_size=extended scope=both sid='*'; 
alter system set max_string_size=extended scope=both sid='*'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration

2. 重啟至 upgrade 模式 (restart oracle to upgrade mode)

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 6442448976 bytes
Fixed Size		    8910928 bytes
Variable Size		 1442840576 bytes
Database Buffers	 4982833152 bytes
Redo Buffers		    7864320 bytes
Database mounted.
Database opened.

3. 調整 max_string_size=extended (alter parameter max_string_size=extended)

SQL>  alter system set max_string_size=extended;

System altered.

4. 執行 $ORACLE_HOME/rdbms/admin/utl32k.sql 之調整檔案 (execute $ORACLE_HOME/rdbms/admin/utl32k.sql)

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.


Session altered.


PL/SQL procedure successfully completed.


STARTTIME
--------------------------------------------------------------------------------
01/26/2022 08:58:27.725708000


PL/SQL procedure successfully completed.

No errors.

Session altered.


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.


Session altered.

5. 重啟服務並建立table測試varchar2(32767)欄位 (restart database and create test table column varchar2(32767))

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

Total System Global Area 6442448976 bytes
Fixed Size		    8910928 bytes
Variable Size		 1442840576 bytes
Database Buffers	 4982833152 bytes
Redo Buffers		    7864320 bytes
Database mounted.
Database opened.

SQL> create table t1 (c1 varchar2(32767));

Table created.

SQL> drop table t1;

Table dropped.

 

Note:

Error: ORA-14695: MAX_STRING_SIZE migration is incomplete

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

Total System Global Area 6442448976 bytes
Fixed Size		    8910928 bytes
Variable Size		 1442840576 bytes
Database Buffers	 4982833152 bytes
Redo Buffers		    7864320 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14695: MAX_STRING_SIZE migration is incomplete
Process ID: 22425
Session ID: 9 Serial number: 8062

Cause :

UNAME
--------------------------------------------------------------------------------
TNAME
--------------------------------------------------------------------------------
CNAME
--------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------
MOF_5
BIN$1k7DlbVTXH/gUwESAQoRdQ==$0
error_reason
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

Solution : 

SQL> shutdown immediate;
SQL> startup upgrade;
SQL> purge dba_recyclebin;
SQL> @?/rdbms/admin/utl32k.sql
SQL> shutdown immediate;
SQL> startup;