在Oracle的以前版本中如果要存储超过4000byte的字符串需要使用clob字段,而lob本身操作就麻烦,而且效率不高。从12C开始Oracle提供了 VARCHAR2, NVARCHAR2, and RAW支持32k长度在字符串,大大提高了Oracle程序在处理4000到32k的字符串的处理效率.
数据库版本
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit 0 PL/SQL Release 12.1.0.0.2 0 CORE 12.1.0.0.2 0 TNS for Linux: Version 12.1.0.0.2 0 NLSRTL Version 12.1.0.0.2 0
max_sql_string_size参数
SQL> show parameter max_sql_string_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_sql_string_size string LEGACY max_sql_string_size:controls maximum size of VARCHAR2, NVARCHAR2, and RAW types in SQL max_sql_string_size, must be from among EXTENDED, LEGACY
创建测试表
SQL> create table t_xifenfei(id number,name varchar2(4001));
create table t_xifenfei(id number,name varchar2(4001))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype
SQL> create table t_xifenfei(id number,name varchar2(4000));
Table created.
SQL> insert into t_xifenfei values(1,rpad('www.orasos.com',4000,0));
1 row created.
SQL> insert into t_xifenfei values(2,lpad('www.orasos.com',4009,0));
1 row created.
SQL> commit;
Commit complete.
SQL> select id,length(name) from t_xifenfei;
ID LENGTH(NAME)
---------- ------------
1 4000
2 4000
SQL> select id,substr(name,-10,10) from t_xifenfei;
ID SUBSTR(NAME,-10,10)
---------- ----------------------------------------
1 0000000000
2 fenfei.com
SQL> select id,substr(name,3990) from t_xifenfei;
ID SUBSTR(NAME,3990)
---------- --------------------------------------------
1 00000000000
2 ifenfei.com
测试说明几点：
1.默认情况下varchar2长度不能超过4000
2.插入varchar2超过4000的字段(列长度为4000),自动被截断
修改max_sql_string_size参数
SQL> alter system set max_sql_string_size='EXTENDED'; alter system set max_sql_string_size='EXTENDED' * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-02096: specified initialization parameter is not modifiable with this option SQL> alter system set max_sql_string_size='EXTENDED' scope=spfile; System altered.
执行utl32k.sql脚本
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup upgrade; ORACLE instance started. Total System Global Area 313159680 bytes Fixed Size 2259912 bytes Variable Size 243270712 bytes Database Buffers 62914560 bytes Redo Buffers 4714496 bytes Database mounted. Database opened. SQL> @?/rdbms/admin/utl32k.sql --包含编译无效对象 QL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 313159680 bytes Fixed Size 2259912 bytes Variable Size 251659320 bytes Database Buffers 54525952 bytes Redo Buffers 4714496 bytes Database mounted. Database opened. SQL> show parameter max_sql_string_size; NAME TYPE VALUE ------------------------------------ ----------- ---------------- max_sql_string_size string EXTENDED
测试varchar2(32767)
SQL> conn xff/xifenfei
Connected.
SQL> create table t_xifenfei_1(id number,name varchar2(32768));
create table t_xifenfei_1(id number,name varchar2(32768))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype
SQL> create table t_xifenfei_extend(id number,name varchar2(32767));
Table created.
SQL> insert into t_xifenfei_extend values(3,lpad('www.orasos.com',32767,0));
1 row created.
SQL> commit;
Commit complete.
SQL> select id,substr(name,-10,10) from t_xifenfei_extend;
ID SUBSTR(NAME,-10,10)
---------- ----------------------------------------
3 fenfei.com
SQL> select id,substr(name,32760) from t_xifenfei_extend;
ID SUBSTR(NAME,32760)
---------- --------------------------------
3 nfei.com
SQL> select id,length(name) from t_xifenfei_extend;
ID LENGTH(NAME)
---------- ------------
3 32767
Oracle 12C支持字符串32K处理过程
1.修改max_sql_string_size=’EXTENDED’
2.重启数据库至upgrade状态
3.执行@?/rdbms/admin/utl32k.sql
4.重启数据库至正常open状态
