[20130915]12c新特性 varchar2支持32K长度.txt
oracle 12c以前如果字符串长度超过4000,必须使用blob或者clob类型。12c开始支持超过4000的字符串长度,提高一些应用的灵活性,
达到32K,避免一些字段定义为clob,blob类型,提高处理能力。
但是12c默认的方式不支持大于4000的字符串长度,必须经过一些步骤升级完成,自己测试如下:
SYS@test> @verBANNER CON_ID-------------------------------------------------------------------------------- ----------Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0SYS@test> show parameter max_string_sizeNAME TYPE VALUE---------------- ------- -----------max_string_size string STANDARDSYS@test> alter system set max_string_size='EXTENDED' ;alter system set max_string_size='EXTENDED'*ERROR at line 1:ORA-02097: parameter cannot be modified because specified value is invalidORA-02095: specified initialization parameter cannot be modified--不能在线修改这个参数。
SYS@test> alter system set max_string_size='EXTENDED' scope=spfile ;
System altered.
--修改spfile的参数文件。shutdown immediate,进入升级模式startup upgrade;,调用@?/rdbms/admin/utl32k.sql。
SYS@test> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SYS@test> startup upgrade;ORACLE instance started.Total System Global Area 1670221824 bytesFixed Size 2403352 bytesVariable Size 1006633960 bytesDatabase Buffers 654311424 bytesRedo Buffers 6873088 bytesDatabase mounted.Database opened.SYS@test> @?/rdbms/admin/utl32k.sql...
--如果存在pdb库,还必须升级PDB$SEED,以及PDB数据库,不然会出现如下提示,在启动时。
ORA-01092: ORACLE instance terminated. Disconnection forcedORA-14696: MAX_STRING_SIZE migration is incomplete for pluggable database PDB$SEEDProcess ID: 1192Session ID: 355 Serial number: 5SYS@test> startup upgradeORACLE instance started.Total System Global Area 1670221824 bytesFixed Size 2403352 bytesVariable Size 1006633960 bytesDatabase Buffers 654311424 bytesRedo Buffers 6873088 bytesDatabase mounted.Database opened.SYS@test> ALTER SESSION SET CONTAINER = PDB$SEED;Session altered.SYS@test> @?/rdbms/admin/utl32k.sql...SYS@test> ALTER SESSION SET CONTAINER = TEST01p;Session altered.SYS@test> alter pluggable database test01p open upgrade;Pluggable database altered.SYS@test> @?/rdbms/admin/utl32k.sql...
--再重新启动数据库,OK。
SYS@test> startupORACLE instance started.Total System Global Area 1670221824 bytesFixed Size 2403352 bytesVariable Size 1023411176 bytesDatabase Buffers 637534208 bytesRedo Buffers 6873088 bytesDatabase mounted.Database opened.SYS@test> alter pluggable database test01p open ;Pluggable database altered.==以scott用户test01p服务:SCOTT@test01p> create table t1 (id number,text varchar2(32767));Table created.SCOTT@test01p> insert into t1 values (1,lpad('a',32767,'b'));1 row created.
--OK,现在可以使用32K的字符串,实际上是32767的长度。
SCOTT@test01p> create table t2 (id number,text varchar2(32768));create table t1 (id number,text varchar2(32768))*ERROR at line 1:ORA-00910: specified length too long for its datatype