oracle 19c max_string_size参数

ORA-39083: Object type TABLE:"hp"."table1" failed to create with error:
ORA-00910: specified length too long for its datatype
复制

对比了源端和目标端，发现源端数据库的national character 为utf-8，而目标端的national character 为al16utf16。查了下资料发下如下说法：

The maximum column size allowed is 4000 characters when the national character set is UTF8 and 2000 when it is AL16UTF16.
复制

SQL> create table ruitest(a nvarchar2(4000));
create table ruitest(a nvarchar2(4000))
                                      *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> select value from nls_database_parameters where parameter ='NLS_NCHAR_CHARACTERSET';

VALUE
----------------------------------------
AL16UTF16                             
复制

2、修改参数

SQL> ALTER SYSTEM SET MAX_STRING_SIZE = EXTENDED SCOPE=SPFILE;

System altered.

SQL> shutdown  immediate;

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

Total System Global Area 1543500120 bytes
Fixed Size                  8925528 bytes
Variable Size             889192448 bytes
Database Buffers          637534208 bytes
Redo Buffers                7847936 bytes
Database mounted.
Database opened.
SQL> @$ORACLE_HOME/rdbms/admin/utl32k;

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
--------------------------------------------------------------------------------
07/10/2024 13:40:10.512464000


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.

SQL> shutdown  immediate;

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

Total System Global Area 1543500120 bytes
Fixed Size                  8925528 bytes
Variable Size             889192448 bytes
Database Buffers          637534208 bytes
Redo Buffers                7847936 bytes
Database mounted.
Database opened.
SQL> create table ruitest(a nvarchar2(4000));

Table created.

SQL> select value from nls_database_parameters where parameter ='NLS_NCHAR_CHARACTERSET';

VALUE
----------------------------------------
AL16UTF16

SQL> 
复制