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