[20210223]sys与Extended Data Types.txt

[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.