12C改varchar2为32K

 

                                       12C的改varchar2为32K

   我们知道12C中的varchar2类型最大可以支持到 32K,但是default是不支持的,由参数MAX_STRING_SIZE来
进行控制,default的值为standard。如果要支持到32K,需要更改为extended.

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

 

对于non-cdb,过程如下:

SQL> alter system set max_string_size=extended scope=spfile;
System altered.

SQL> shutdown immediate;

SQL> startup upgrade;

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

执行完成后，重启DB生效

 

对于cdb模式下，在执行完后不能重启到正常模式，否则会报如下错
误.这是因为PDB$SEED的该参数要和CDB参数一致，所以PDB$SEED也要
升级一次
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14696: MAX_STRING_SIZE migration is incomplete for pluggable database
PDB$SEED
Process ID: 16370
Session ID: 240 Serial number: 11

在以upgrade模式执行完utl32k.sql后，关闭instance,然后再mount instance.

SQL> startup mount;

SQL>alter database open migrate;

Database altered.

SQL> alter session set container=PDB$SEED;

Session altered.

SQL> alter system set max_string_size=extended;

System altered.

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

Session altered.

再次重启instance。