[20210223]sys与Extended Data Types.txt

--//12c开始支持大于4000字符的字符串,但是缺省并不支持必须经过一些步骤升级完成.参考链接
--//如下:http://blog.itpub.net/267265/viewspace-772855/=>[20130915]12c新特性 varchar2支持32K长度.txt
--//实际上sys不受这个限制,可以建立varchar2(32768)类型,即使参数在max_string_size  =STANDARD的情况下.
--//参考链接 https://mvelikikh.blogspot.com/2021/01/sys-and-extended-data-types.html
--//通过测试说明问题:

1.环境:
SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.测试:
SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> show parameter max_string_size
NAME            TYPE   VALUE
--------------- ------ --------
max_string_size string STANDARD

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> create table t(c varchar2(32767));
Table created.
--//可以发现即使是max_string_size=STANDARD,也可以定义数据类型varchar2(32767).

3.能插入数据吗?
SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> insert into t values (lpad('x',32767,'x'));
1 row created.

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> commit ;
Commit complete.
--//视乎插入成功.

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> select length(c) from t;
 LENGTH(C)
----------
      4000

--//而实际上仅仅插入4000个字符.实际上这个是lpad的限制,最大4000个字符.

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> update t set c=lpad('x',4000,'x')||lpad('y',4000,'y');
update t set c=lpad('x',4000,'x')||lpad('y',4000,'y')
       *
ERROR at line 1:
ORA-01489: result of string concatenation is too long

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> update t set c=c||'y';
1 row updated.

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> select length(c) from t;
 LENGTH(C)
----------
      4001

--//OK,说明可以超过4001限制.

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> update t set c=c||lpad('y',4000,'y');
1 row updated.

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> select length(c) from t;
 LENGTH(C)
----------
      8001

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> rollback ;
Rollback complete.

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> update t set c=c||c;
1 row updated.

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> update t set c=c||c;
1 row updated.

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> update t set c=c||c;
1 row updated.

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> update t set c=c||c;
update t set c=c||c
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> select length(c) from t;
 LENGTH(C)
----------
     32000

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> update t set c=c||lpad('x',768,'x');
update t set c=c||lpad('x',768,'x')
       *
ERROR at line 1:
ORA-01489: result of string concatenation is too long

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> update t set c=c||lpad('x',767,'x');
1 row updated.

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> select dbms_metadata.get_ddl('TABLE', 'T') from dual;
DBMS_METADATA.GET_DDL('TABLE','T')
-----------------------------------------------------------------------------
  CREATE TABLE "SYS"."T"
   (    "C" VARCHAR2(32767)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"

SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> select column_name, segment_name, index_name, securefile from user_lobs where table_name='T'
  2  @ prxx
==============================
COLUMN_NAME                   : C
SEGMENT_NAME                  : SYS_LOB0000102997C00001$$
INDEX_NAME                    : SYS_IL0000102997C00001$$
SECUREFILE                    : NO
PL/SQL procedure successfully completed.
--//实际上oracle内部使用lob保存.
 
SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> drop table t purge ;
Table dropped.

--//还可以发现有一些列已经超过4000.
SYS@192.168.xx.xx:1521/xxnnn/xxnnn2> select table_name, column_name, data_length from user_tab_cols where data_type = 'VARCHAR2' and data_length > 4000
  2  @ prxx
==============================
TABLE_NAME                    : SYSDBIMFS_METADATA$
COLUMN_NAME                   : VALUE
DATA_LENGTH                   : 4096
==============================
TABLE_NAME                    : OPATCH_SQL_PATCHES
COLUMN_NAME                   : NODE_NAMES
DATA_LENGTH                   : 32000
==============================
TABLE_NAME                    : V_$DIAG_LOG_EXT
COLUMN_NAME                   : SUPPLEMENTAL_DETAILS
DATA_LENGTH                   : 4003
PL/SQL procedure successfully completed.