ORACLE 12C varchar2支持32k长度字符串

联系:手机(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:ORACLE 12C varchar2支持32k长度字符串

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在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.xifenfei.com',4000,0));

1 row created.

SQL> insert into t_xifenfei values(2,lpad('www.xifenfei.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.xifenfei.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状态

此条目发表在 ORACLE 12C 分类目录,贴了 标签。将固定链接加入收藏夹。

ORACLE 12C varchar2支持32k长度字符串》有 3 条评论

  1. liango 说:

    我也想用ORACLE 12C

      [引用]  [回复]

  2. 晴刃 说:

    博主是个数据库高手,俺最近正在学习~先收藏了~

      [引用]  [回复]

  3. 惜分飞 说:

    如果是pdb模式,处理步骤如下

    1.Shut down the CDB.
    2.Restart the CDB in UPGRADE mode.
    3.Change the setting of MAX_STRING_SIZE to EXTENDED.
    4.Run the rdbms/admin/utl32k.sql script in the root container and in every PDB in the CDB. 
    You must be connected AS SYSDBA to run the utl32k.sql script. 
    Use the catcon.pl script to run the utl32k.sql script in the root container and every PDB in the CDB.
    

      [引用]  [回复]

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>