[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.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2758527/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20210223]bbed itl ktbitflg 2.txt
- Extended Window Manager Hints(1)
- Oracle 21C Extended ClustersOracle
- Oracle中Nextval用法SEQUENCE與SYS_GUID()OracleGUI
- COPY & SYS
- 介紹兩種遊標cursor與sys_refcursor
- link/Extended dependency 無法顯示連線
- 1.5.2.2. SYS
- Python--sysPython
- 詳解Python中sys模組的功能與應用Python
- ORACLE ORA-01017&SP2-0157與sys密碼重置Oracle密碼
- Linux 格式化擴充套件分割槽(Extended)Linux套件
- 4.1.6 Oracle Restart 與 Oracle Data Guard 整合OracleREST
- 儲存與索引------《Designing Data-Intensiv索引
- Raft論文《 In Search of an Understandable Consensus Algorithm (Extended Version) 》研讀RaftGo
- Kubernetes Extended Resource 擴充套件資源使用簡介套件
- Streaming Data Warehouse 儲存:需求與架構架構
- Big Data and Data Warehousing
- MySQL sys效能監控MySql
- 微信小程式--data的賦值與取值微信小程式賦值
- torch.utils.data.DataLoader與迭代器轉換
- ORA-04063: package body SYS.DBMS_REGISTRY_SYS has errors (Doc ID 2165402.1)PackageError
- 轉:os和sys模組
- 202403172356_《Initial sys. of C》
- sys_stat_activity檢視
- setup與data、methods三者間的關係
- 18 與Oracle Data Guard 相關的SQL語句OracleSQL
- form-data與x-www-form-urlcoded區別ORM
- 資料網格與Data Fabric的區別 - thenewstack
- 字串與數字轉換函式 | 全方位認識 sys 系統庫字串函式
- linux環境下sqlplus sys/sys@ORCL as sysdba報錯 ORA-01031: insufficient privilegesLinuxSQL
- postgresSQL Extended Query執行過程和sharding-proxy的處理SQL
- sys_context函式的用法Context函式
- golang.org/x/sys/unix: unrecognizedGolangZed
- mysql 5.7 sys資料庫初探MySql資料庫
- [20211112]SYS_CONTEXT ('USERENV','DBID').txtContext
- data URI
- data structureStruct