행위

Oracle 언어설정

DB CAFE

1 Oracle DataBase 문자셋과 언어셋 확인 및 변경

1.1 개요

1.1.1 NLS_LANG

 attach_file - NLS_LANG = [언어]_[영역].[캐릭터셋]

            <language>_<Territory>.<client characterset>
   ex) AMERICAN_ARERICA.KO16KSC5601, AMERICAN_AMRRICA.UTF8

1.1.2 NLS_LANG 설명

 ● <Territory> - (국가) 영역정보, 달력 설정 방법, 통화기호(달라), 숫자그룹
                  날짜 포맷(한국 - "05/08/12 오후 07:28:03", 체코 - "10.08.05 19:28:03")
                  Language 값만 설정하면, Language 값을 따른다. ( AMERICAN ==> AMERICA )
 ● <language> - 캐릭터셋, 정렬방식, 날짜 표기에 사용되는 기호(년/월/일, YYYY/MM/DD) 
                 Default 값은 AMERICAN 이다.
                 실제 날짜 표기(TO_CHAR,TO_DATE 실행시 - 월요일, MON 으로 표시를 좌우하는
                 NLS_DATE_FORMAT 값은 NLS_LANGUAGE 을 따른다.
 ● <client characterset>
   ◇ 기본적으로 Server characterset 과 동일하게 설정된다.
   - 클라이언트에서 캐릭터셋만 설정하면, 디폴트로 AMERICAN_AMERICA 로 Territory_language 값이 설정된다.
   ◇ Client Characterset 을 Server 와 다르게 설정 상태에서,
     SQL> SELECT SYS_CONTEXT('USERENV','LANGUAGE') "NLS SET" from dual;
     을 실행 하여도, 항상 CharacterSet 은 항상 서버 CharacterSet 값을 가져온다.
   ◇ client characterset을 서버와 다르게 하는 경우는 크게
     1. 다른 캐릭터셋 DB로 데이타 이전시(EXPORT/IMPORT)
        △  EXPORT/IMPORT 시에는 반드시 서버의 Character Set 동일하게 설정 후 Export/Import 한다.
        △  다른 캐릭터셋의 DB(데이타가 깨지지 않고, 지원되는 경우, SubSet Characterset 일경우)로 IMPORT 시에는
          Import 대상의 DB 캐릭터셋으로 환경 설정 후, Export/Import 한다.
     2. Server(UTF8) - Client(KO16KSC5601)
        △ 다국어 지원되는 데이타베이스에다 한글 입출력 하기 위해서는
           입력시 - 내가 입력 하는 데이타의 캐릭터셋이 한글임을 알리기 위해서,
           출력시 - Unicode 의 Data를 한글로 표현해 달라고 하기 위해서.


1.2 문자셋 확인

-- NLS_LANG 조회         
SELECT (SELECT value 
        FROM   nls_database_parameters 
        WHERE  parameter = 'NLS_LANGUAGE') 
       || '_' 
       || (SELECT value 
           FROM   nls_database_parameters 
           WHERE  parameter = 'NLS_TERRITORY') 
       || '.' 
       || (SELECT value 
           FROM   nls_database_parameters 
           WHERE  parameter = 'NLS_CHARACTERSET') NLS_LANG 
FROM   dual
-- NLS_LANGUAGE/ NLS_CHARACTERSET / NLS_NCHAR_CHARACTERSET
SELECT name
     , value$
  FROM sys.props$
 WHERE name in('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

1.3 문자셋 변경(SERVER)

  • Oracle DataBase 관리자로 접속 NLS_CHARACTERSET, NCHAR의 CHARACTERSET에 원하는 문자셋을 변경
UPDATE sys.props$
   SET value$ = '[ 변경을 원하는 문자셋 (KO16KSC5601, AL32UTF8 등)]'
 WHERE name = 'NLS_CHARACTERSET';
UPDATE sys.props$
   SET value$ = '[ 변경을 원하는 문자셋 (KO16KSC5601, AL32UTF8 등)]'
 WHERE name = 'NLS_NCHAR_CHARACTERSET';

1.4 언어셋 확인

SELECT name, value$
  FROM sys.props$
 WHERE name = 'NLS_LANGUAGE'; -- AMERICAN , KOREAN

1.5 언어셋 변경(SERVER)

  • 오라클 데이터베이스 관리자로 접속하여 NLS_LANGUAGE에 원하는 언어셋을 변경
UPDATE sys.props$
   SET value$ = '[ 변경을 원하는 문자셋 (AMERICAN_AMERICA.KO16KSC5601, AMERICAN_AMERICA.AL32UTF8등)]'
 WHERE name = 'NLS_LANGUAGE'';
####################################################
#### .bash_profile 환경변수

export ORACLE_BASE=/home/oracle
export ORACLE_HOME=$ORACLE_BASE/oracle/product/10.2.0/db_1
export ORACLE_SID=ora10
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
#export LD_ASSUME_KERNEL=2.4.19
export PATH=$PATH:$ORACLE_HOME/bin

export NLS_LANG=KOREAN_KOREA.KO16KSC5601 #한글
export NLS_LANG=KOREAN_KOREA.KO16MSWIN949 #한글(추천:지원캐릭터가 더 많음 -뷃,숖..)
export NLS_LANG=AMERICAN_AMERICA.UTF8 #유니코드
####################################################
#### 캐릭터 셋 설정 확인

SELECT NAME,VALUE$ FROM PROPS$ WHERE NAME ='NLS_LANGUAGE' OR NAME ='NLS_TERRITORY' OR NAME ='NLS_CHARACTERSET';


####################################################
#### 오라클 캐릭터 셋 변경

update props$ set VALUE$='UTF-8' where name='NLS_CHARACTERSET';
update props$ set VALUE$='KO16MSWIN949' where name='NLS_CHARACTERSET';
update props$ set VALUE$='KO16KSC5601' where name='NLS_CHARACTERSET';

update props$ set VALUE$='KOREAN' where name='NLS_LANGUAGE';
update props$ set VALUE$='KOREA' where name='NLS_TERRITORY';

2 캐릭터셋 확인 쿼리

select distinct(nls_charset_name(charsetid)) CHARACTERSET
     , decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),9
                      , decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),96
                      , decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),112
                      , decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')
       ) TYPES_USED_IN
  from sys.col$ 
 where charsetform in (1,2) 
   and type# in (1, 9, 96, 112);
  1. VARCHAR2 타입
    VARCHAR2 는 문자열의 바이트를 기준으로 하기 때문에 ASCII Code 에 있는 문자(1Byte)인지 한글(2BYTE)인지에 따라 저장할 수 있는 단어수가 달라짐.
  2. NVARCHAR 타입
    NVARCHAR2 타입을 사용할 경우 문자열의 바이트가 아닌 문자 갯수 자체를 길이로 취급하므로 한글, 영어, 기타 외국어 여부와 상관없이 처리할 수 있음.

2.1 캐릭터셋 확인 쿼리 결과(잘못된 경우):

  • varchar2가 2개 의 캐릭터셋이 설정되어있음.
CHARACTERSET TYPES_USED_IN
-----------------------------------------------------
AL16UTF16 NCHAR
AL16UTF16 NVARCHAR2
AL16UTF16 NCLOB
US7ASCII CHAR
US7ASCII VARCHAR2
WE8DEC VARCHAR2
US7ASCII CLOB

2.2 캐릭터셋 확인 쿼리 결과(정상인 경우):

  • TYPES_USERD_IN 하나당 하나의 캐릭터셋
CHARACTERSET TYPES_USED_IN
-----------------------------------------------------
AL16UTF16 NCHAR
AL16UTF16 NVARCHAR2
AL16UTF16 NCLOB
AL32UTF8 CHAR
AL32UTF8 VARCHAR2
AL32UTF8 CLOB

2.3 해결 방법 및 절차  :

1. INIT.ORA 안에 있는 parallel_server parameter 가 false 거나 아예 세팅되어있지 않은지 확인.

SQL>show parameter parallel_server

2. sqlplus "/as sysdba"로 다음 쿼리 실행(기존 데이터 백업 필수)

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
COL VALUE NEW_VALUE CHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
COL VALUE NEW_VALUE NCHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';

-- UTF8로 바꿀 경우(선택)

ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;

-- 한글로 바꿀 경우(선택)

ALTER DATABASE CHARACTER SET INTERNAL_USE KO16MSWIN949
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;

-- oracle reboot 2번.

SHUTDOWN IMMEDIATE;
STARTUP;
SHUTDOWN IMMEDIATE;
STARTUP;

3. parallel_server parameter 수정한 경우 원복.

2.4 참조 URL

  1. US7ASCII 캐릭터셋 변경 https://javafactory.tistory.com/242

[오라클] 한글이 서로 맞지 않을때 오라클에서 사용 할 수 있는 방법


UTL_RAW.CAST_TO_RAW /* 한글을 16진수 핵사 코드로 변환 */

RAW.CAST_TO_VARCHAR2 /* 16진수 핵사 한글로 변환 */


select '홍길동', UTL_RAW.CAST_TO_RAW('홍길동') from dual;
select UTL_RAW.CAST_TO_VARCHAR2('C8ABB1E6B5BF') from dual;