Difference between VARCHAR2(n),VARCHAR2(n BYTE),VARCHAR2(n CHAR)

/* 20080122
　　Difference between VARCHAR2(n),VARCHAR2(n BYTE),VARCHAR2(n CHAR)
　　reference url:
　　http://www.oracle-base.com/articles/9i/CharacterSemanticsAndGlobalization9i.php
　　使用 ORACLE 參數NLS_LENGTH_SEMANTICS
　　設定值: BYTE / CHAR
　　from:　nls_database_parameters
　　　　　　nls_session_parameters
　　　　　　nls_instance_parameters
*/


/*　當 ORACLE 參數 NLS_LENGTH_SEMANTICS = BYTE 時
　　NOTE: VARCHAR2(n) = VARCHAR2(n BYTE)
*/

CREATE TABLE TBLA
(
COL1 VARCHAR2(20) -- 等同TBLB之設定VARCHAR2(20 BYTE) 即看BYTE 不看字數
);

INSERT INTO TBLA VALUES ('一二三四五六七');

=> 執行後錯誤訊息如下:

ORA-12899: 資料欄 "ORION"."TBLA"."COL1" 的值太大 (實際: 21, 最大值: 20)


INSERT INTO TBLA VALUES ('一二三四五六');

=>執行ok

CREATE TABLE TBLB
(
COL1 VARCHAR2(20 BYTE)
);
INSERT INTO TBLB VALUES ('一二三四五六');

=>執行ok

CREATE TABLE TBLC
(
COL1 VARCHAR2(20 CHAR) -- 看字數不看BYTE數
);

INSERT INTO TBLC VALUES ('一二三四五六七八九十一二三四五六七八九十');

=>執行ok

-- 檢查TABLE使用之EXTENT => 以資料量來說看不出來

SELECT TABLESPACE_NAME, SEGMENT_NAME, EXTENTS,MAX_EXTENTS,
　　　　(EXTENTS/MAX_EXTENTS)*100 AS USED_EXTS
FROM 　USER_SEGMENTS
WHERE 　SEGMENT_NAME IN ('TBLA','TBLB','TBLC');

-- 檢查欄位BYTE數

SELECT 'TBLA' TBLNAME,SUM(VSIZE(COL1)) TBLSIZE FROM TBLA
UNION ALL
SELECT 'TBLB' TBLNAME,SUM(VSIZE(COL1)) TBLSIZE FROM TBLB
UNION ALL
SELECT 'TBLC' TBLNAME,SUM(VSIZE(COL1)) TBLSIZE FROM TBLC;

-- 執行結果

TBLNAME　　　TBLSIZE
----------- ---------
TBLA　　　　　18
TBLB　　　　　18
TBLC　　　　　60

-- TBLA LAYOUT

DESC TBLA

名稱　　　　　　　空值?　　類型
--------------- ------- ------------------------
COL1　　　　　　　　　　　VARCHAR2(20)


/*　將SESSION 的設定改掉　*/

ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR;

/*　因為 ORACLE 參數 NLS_LENGTH_SEMANTICS = CHAR 時
NOTE: VARCHAR2(n) = VARCHAR2(n CHAR)
*/

CREATE TABLE TBLD
(
COL1 VARCHAR2(20) -- 等同TBLC之設定VARCHAR2(20 CHAR) 即字數不看BYTE數
);


-- TBLD LAYOUT

名稱　　　　　　　空值?　　類型
--------------- ------- ------------------------
COL1　　　　　　　　　　　VARCHAR2(20)

--再看一下改SESSION設定後TBLA LAYOUT

DESC TBLA

名稱　　　　　　　空值?　　類型
--------------- ------- ------------------------
COL1　　　　　　　　　　　 VARCHAR2(20 BYTE)

-- 再將SESSION 改BYTE

ALTER SESSION SET NLS_LENGTH_SEMANTICS = BYTE;

-- 再看一下 TABLE TBLD LAYOUT

名稱　　　　　　　空值?　　類型
--------------- ------- ------------------------
COL1　　　　　　　　　　　 VARCHAR2(20 CHAR)