[오라클] 캐릭터셋 개념 및 데이터 타입과 byte

- 오라클에서 캐릭터셋 확인하는 쿼리: 동적 성능 뷰 V$NLS_PARAMETERS에서 또는 PROPS$, NLS_DATABASE_PARAMETERS에서 정보를 확인할 수 있습니다. 

SQL> select * from v$nls_parameters where parameter like '%CHARACTER%';

PARAMETER               VALUE          CON_ID
----------------------- ---------- ----------
NLS_NUMERIC_CHARACTERS  .,                  1
NLS_CHARACTERSET        AL32UTF8            1
NLS_NCHAR_CHARACTERSET  AL16UTF16           1

 

또는

SQL> col value$ for a30
SQL> select name, value$ from props$ where name like 'NLS_CHARACTERSET%';

NAME                                     VALUE$
---------------------------------------- ---------
NLS_CHARACTERSET                         AL32UTF8

 

또는

SQL> col value for a15
SQL> select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

VALUE
---------------
AL32UTF8

 

1. AL32UTF8은 오라클에서 사용하는(불리우는) 데이터베이스 캐릭터셋이고 유니코드를 지원합니다. 즉, 모든 CHAR, VARCHAR2, LONG, CLOB 그리고 NCHAR, NVARCHAR2, NCLOB 데이터 타입이 "유니코드"입니다. 

 

- 참고: AL16UTF16은 NLS_CHARACTERSET으로 사용할 수 없는 NLS_NCHAR_CHARACTERSET으로만 사용 가능한 캐릭터셋이다. 

 

2. 모든 클라이언트 및 서버가 9i 이상일 경우 UTF8이 아닌 AL32UTF8을 사용할 것을 권장합니다. 

 

3. AL32UTF8은 너비가 다양한 캐릭터셋입니다. 즉, 1 문자에 대한 코드는 1, 2, 3 또는 4바이트가 될 수 있습니다. 이는 1 문자가 항상 1바이트인 WE8ISO8859P1 또는 WE8MSWIN1252 등의 캐릭터셋과의 중요한 차이점입니다.

 

4. US7ASCII 문자(A-Z, a-Z, 0-1 및 ./?,*# 등)는 AL32UTF8에 속하며 항상 1바이트입니다. 이는 "특수" 문자만 8비트 캐릭터셋에서보다 더 많은 바이트를 사용하며, 특수 문자는 A-Z에 비해 대부분의 서양 언어에서 그리 자주 사용되지 않기 때문입니다.
키릴 자모 또는 아랍어 체계를 AL32UTF8로 변환할 때는 모든 키릴 자모 또는 아랍어 데이터를 저장하는 데 훨씬 더 많은 바이트가 사용됩니다. 전체 데이터 집합에 대한 영향도 더 큽니다.

US7ASCII(A-Z, a-Z, 0-1 및 ./?,*# 등) 외 문자의 경우 같은 문자를 저장하는 데 더 많은 "바이트"가 사용되므로 컬럼 레벨에서 그 영향이 클 수 있습니다.   

컬럼은 추가 바이트를 저장할 수 있도록 충분히 커야 합니다. 기본적으로 컬럼 크기는 CHARACTERS 대신 BYTES로 정의됩니다.
기본적으로 "create table(<colname> VARCHAR2 (300));"은 컬럼에 300바이트를 저장할 수 있다는 의미입니다.
300바이트를 저장할 수 있다는 것은 "a"와 같은 1바이트 문자 300개나 €와 같은 3바이트 문자를 100 번 저장할 수 있다는 의미입니다. 3바이트 문자를 101번 저장하려고 하면 ORA-01401 / ORA-12899가 표시됩니다. 이러한 101개 문자의 바이트 길이는 300이 넘습니다.

9i부터는 캐릭터셋에 관계없이 저장할 문자 수로 컬럼 길이를 정의할 수 있습니다.

참고: UTF8은 1, 2, 3 또는 6바이트/문자가 될 수 있습니다. 4바이트인 모든 AL32UTF8 문자는 UTF8에 2 x 3바이트로 저장됩니다. UTF8에서 6바이트가 되고 AL32UTF8에서 4바이트가 되는 "실제" 문자의 양은 제한되어 있으며 일부 중국어 문자를 사용할 경우에만 존재합니다. 

 

참고: 문자의 최대 바이트 길이는 UTF8의 경우 3, AL32UTF8의 경우 4이지만 AL32UTF8에서 3바이트 넘게 공간을 차지하는 문자를 정의하는 8비트 캐릭터셋은 없으므로(예: 유로 기호는 UTF8/AL32UTF8에서 최대 3바이트임) 데이터에 대해 가능한 최대 바이트 증가분은 대상 db가 UTF8 또는 AL32UTF8 중 어떤 것인지에 관계없이 3입니다(8비트 캐릭터셋으로 인코딩된 모든 데이터의 경우).

 

5. UTF8과 AL32UTF8 차이점: UTF8은 보조 문자를 2자로 저장하여 총 6 Bytes가 되고 수정된 UTF-8이 표준 UTF-8 대신 보조 문자에 사용됩니다. AL32UTF8은 이러한 보조 문자에 대한 기본 지원이 포함됩니다. 즉, 저장 데이터에 대해 두 캐릭터셋은 동일하고 실제 저장 바이트/코드에서 다름을 의미합니다. 클라이언트측에서 이미 보조 문자가 사용되는 경우는 많지 않습니다. "실제 상황"에서는 주로 HKSCS 2001/2004(홍콩 확장) 캐릭터셋이 있는 윈도 시스템이 그렇습니다. AL32UTF8 에 추가된 기능은 현재 HKSCS 를 현재 사용하지 않더라도 향후 HKSCS 를 사용할 경우를 대비할 수 있고 (거의 모든 데이터 변환이 필요없이 - 다운타임 최소화) 간단히 변경할 수 있습니다.

 

6. 일부 버전에 제약 사항이 있지만 AL32UTF8로의 변환을 수행하기 위한 DMU GUI 툴이 있습니다. DMU는 보다 사용자에게 친숙하고 exp/imp 없이 변환을 수행할 수 있으며 데이터를 수정해야 할 때 더 낫습니다. 전체 exp/imp를 새 AL32UTF8 db에 사용하는 옵션은 보통 새로운 서버/OS로 이전할 때 사용합니다. csscan/csalter/partial exp/imp는 보통 스크립팅을 좋아하는 사용자들이 선호합니다.

 

7. 데이터베이스 이름 및 데이터베이스 링크 이름으로 비US7ASCII 이름을 절대 사용하지 않는 것이 좋습니다. 즉, "가변 너비" 컬럼 아래에 "아니오"가 있는 이름 유형의 경우 AL32UTF8 데이터베이스에서 US7ASCII 문자(a-z, A-Z, 1-0)만 사용해야 합니다. 일반적으로 데이터베이스 객체에 대해 최대한 비US7ASCII 문자를 사용하지 않는 것이 좋습니다. 

 

객체 이름은 1~30바이트 길이일 수 있으나 다음과 같은 예외 사항이 있습니다:
 데이터베이스 이름은 8바이트로 제한됩니다.
 데이터베이스 링크 이름은 최대 128바이트가 될 수 있습니다.

 

8. AL32UTF8 db에서 구체화된 뷰를 사용하거나 데이터베이스 링크를 통해 테이블에 대해 CTAS(Create Table As Select)를 수행하고 데이터베이스 링크가 비AL32UTF8 db를 가리키면 일부 컬럼의 크기가 바뀔 수 있습니다.

AL32UTF8 db에서 구체화된 뷰/CTAS 테이블을 생성할 때 BYTE를 사용해야 하고 컬럼 크기를 동일하게 유지해야 할 경우에는 AL32UTF8 db측에서 _keep_remote_column_size=true 파라미터를 설정할 수 있습니다.
_keep_remote_column_size=true를 사용하면 데이터 집합에 비US7ASCII 문자가 있을 때 ORA-1401 또는 ORA-12899오류가 발생할 수 있습니다. 이는 비US7ASCII 데이터가 증가하기 때문입니다.
이는 소스가 US7ASCII 데이터베이스이지만 US7ASCII에 비US7ASCII *코드*가 포함된 경우에도 발생할 수 있습니다.
소스 데이터베이스에 있는 테이블/사용자에 대해 csscan TOCHAR=AL32UTF8을 실행하여 미리 검사할 수 있습니다. "잘라내기" 데이터가 있으면 ORA-1401 또는 ORA-12899 오류가 표시되고 "무변경" 및/또는 "변환 가능" 데이터만 있고 "잘라내기" 데이터는 없으면 소스측 컬럼 정의에서 _keep_remote_column_size=true를 BYTE 의미와 함께 사용하는 경우에도 ORA-1401 또는 ORA-12899 오류가 표시되지 않습니다. 

 

9. 일반적으로 AL32UTF8로 전환 시 총 메모리 증가 범위는 CHAR 의미 및 AL32UTF8 사용 시 20~30%.

 

10. NLS_LANG이 UTF8 또는 AL32UTF8이어야 한다고 생각하기 쉽습니다. 이것이 항상 옳은 것은 아닙니다. 사실  NLS_LANG은 데이터베이스 캐릭터셋과 전혀 관련이 없습니다. NLS_LANG은 Oracle이 클라이언트 인코딩(=NLS_LANG)과 데이터베이스 캐릭터셋(NLS_CHARACTERSET) 간에 필요한 변환을 수행할 수 있도록 클라이언트 캐릭터셋이 무엇인지 Oracle에 알리기 위한 것입니다.

동일하게 NLS_LANG을 JAPANESE_JAPAN.WE8MSWIN1252로 설정한다고 해서 일본어를 저장할 수 있는 것은 *아닙니다*.

---

DUMP는 데이터 타입 코드, 길이(바이트) 및 expr의 내부 표현을 포함하는 VARCHAR2 값을 반환합니다. 반환된 결과는 항상 데이터베이스 캐릭터 셋에 있습니다. 각 코드에 해당하는 데이터 유형은 SQL Language Reference 표 2-1을 참조하십시오.

 

예:

COL DUMMY FOR A20
COL DMP FOR A20
COL DMP_2 FOR A25
SELECT DUMMY
      , DUMP(DUMMY) AS DMP
      , '썊'
      , DUMP('썊') AS DMP_2
FROM DUAL;

 

예시 결과:

DUMMY                DMP                  '썊 DMP_2
-------------------- -------------------- --- -------------------------
X                    Typ=1 Len=1: 88      썊  Typ=96 Len=3: 236,141,138

 

UTL_RAW 패키지는 RAW 데이터 유형을 조작하기 위한 SQL 함수를 제공합니다. 그리고 CAST_TO_RAW는 일부 데이터 바이트 수를 사용하여 표현된 VARCHAR2 값을 해당 데이터 바이트 수를 가진 RAW 값으로 변환합니다. 데이터 자체는 어떤 식으로든 수정되지 않지만 데이터 유형은 RAW 데이터 유형으로 다시 RECAST됩니다.

예: 

COL CHA_TO_RAW FOR A20
COL RAW_TO_CHA FOR A20
SELECT 
      UTL_RAW.CAST_TO_RAW('썊') AS CHA_TO_RAW
      , UTL_RAW.CAST_TO_VARCHAR2('EC8D8A') AS RAW_TO_CHA
      , UTL_RAW.CAST_TO_RAW('A') AS CHA_TO_RAW      
      , UTL_RAW.CAST_TO_VARCHAR2('41') AS RAW_TO_CHA
FROM DUAL;

 

예시 결과: 

CHA_TO_RAW           RAW_TO_CHA           CHA_TO_RAW           RAW_TO_CHA
-------------------- -------------------- -------------------- ------------
EC8D8A               썊                   41                   A

 

CONVERT('컬럼명', 'KO16KSC5601', 'UTF8')는 "UTF8 -> KO16KSC5601"로 바꾸겠다는 내용입니다.
US7ASCII는 영문 케릭터셋으로 한글을 저장할 수 없는 케릭터셋인데, 한글 데이터를 저장하고 관리한 시스템이 존재합니다. 캐릭터셋은 US7ASCII지만, 실제로 "완성형 + 한글 윈도우즈 코드" 페이지(KO16MSWIN949)의 데이터를 저장해 온 경우 데이터는 현재 상태에서 가공하지 않은 채, 딕셔너리에 있는 캐릭터셋 정보만을 강제로 KO16MSWIN949로 변환하는 방식으로 활용 할 수 있습니다. US7ASCII 데이터베이스에 한글을 저장하게 될 경우, 한 글자당 2바이트로 저장됩니다. 일반적으로 사용하는 클라이언트는 대부분 Windows-949(한글 Windows OS)나 KSC5601 완성형(UNIX계열)이고 이들은 모두 2바이트입니다. 
이 Select 문에서 ORA-01482 에러가 발생하면 지정한 CHARACTER SET이 지원되지 않는 경우입니다. 
그러므로 아래의 경우로 보아 현재는 한글과 한자에 대해 UTF8만을 지원함을 알 수 있습니다. 

SELECT
        CONVERT('가', 'US7ASCII') AS CNVT
      , LENGTHB(CONVERT('가', 'US7ASCII')) AS CNVT_LENB      
	  , TO_CHAR('US7ASCII') AS CHARSET
FROM DUAL
UNION ALL
SELECT 
        CONVERT('가', 'KO16KSC5601')
      , LENGTHB(CONVERT('가', 'KO16KSC5601'))
	  , TO_CHAR('KO16KSC5601')
FROM DUAL
UNION ALL
SELECT 
        CONVERT('가', 'KO16MSWIN949')
      , LENGTHB(CONVERT('가', 'KO16MSWIN949')) 
	  , TO_CHAR('KO16MSWIN949')
FROM DUAL
UNION ALL
SELECT 
        CONVERT('가', 'UTF8')
      , LENGTHB(CONVERT('가', 'UTF8')) 
	  , TO_CHAR('UTF8')
FROM DUAL
UNION ALL
SELECT
        CONVERT('썊', 'US7ASCII')
      , LENGTHB(CONVERT('썊', 'US7ASCII'))
	  , TO_CHAR('US7ASCII')
FROM DUAL
UNION ALL
SELECT
        CONVERT('썊', 'KO16KSC5601')
      , LENGTHB(CONVERT('썊', 'KO16KSC5601'))
	  , TO_CHAR('KO16KSC5601')
FROM DUAL
UNION ALL
SELECT
        CONVERT('썊', 'KO16MSWIN949')
      , LENGTHB(CONVERT('썊', 'KO16MSWIN949'))
	  , TO_CHAR('KO16MSWIN949')
FROM DUAL
UNION ALL
SELECT
        CONVERT('썊', 'UTF8')
      , LENGTHB(CONVERT('썊', 'UTF8'))
	  , TO_CHAR('UTF8')
FROM DUAL
UNION ALL
SELECT 
      CONVERT('禽流可传人', 'US7ASCII')
      , LENGTHB(CONVERT('禽流可传人', 'US7ASCII'))
	  , TO_CHAR('US7ASCII')
FROM DUAL
UNION ALL
SELECT 
      CONVERT('禽流可传人', 'KO16KSC5601')
      , LENGTHB(CONVERT('禽流可传人', 'KO16KSC5601'))
	  , TO_CHAR('KO16KSC5601')
FROM DUAL
UNION ALL
SELECT  
      CONVERT('禽流可传人', 'KO16MSWIN949')
      , LENGTHB(CONVERT('禽流可传人', 'KO16MSWIN949')) 
	  , TO_CHAR('KO16MSWIN949')
FROM DUAL
UNION ALL
SELECT  
      CONVERT('禽流可传人', 'UTF8')
      , LENGTHB(CONVERT('禽流可传人', 'UTF8')) 
	  , TO_CHAR('UTF8')
FROM DUAL;

 

예시 결과:

CNVT             CNVT_LENB CHARSET
--------------- ---------- ------------
?                        1 US7ASCII
▒▒                       2 KO16KSC5601
▒▒                       2 KO16MSWIN949
가                       3 UTF8
?                        1 US7ASCII
▒▒                       2 KO16KSC5601
▒w                       2 KO16MSWIN949
썊                       3 UTF8
?????                    5 US7ASCII
SP2-0784: Invalid or incomplete character beginning 0xEC returned
SP2-0784: Invalid or incomplete character beginning 0xEC returned
禽流可传人              15 UTF8

12 rows selected.

 

AL32UTF8 / UTF8(유니코드) 데이터베이스 캐릭터셋 개념 참고:  https://support.oracle.com/knowledge/Oracle%20Database%20Products/1607876_1.html

AL32UTF8 / UTF8(유니코드) 데이터베이스 캐릭터셋 개념

 

캐릭터셋 블로그글 참고:

https://zenice.tistory.com/8

오라클과 NLS의 찰떡궁합 들여다보기 , Part 2