Oracle Character set 변경

DB 2009. 2. 12. 14:28



####################################################
#### .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';
   


####################################################
#### 캐릭터 셋 변경후에 확인 사항(필수)

    에러 유형 :
        EXP-00008: ORACLE 오류 6552가 발생했습니다
        ORA-06552: PL/SQL: Compilation unit analysis terminated
        ORA-06553: PLS-553: 알 수 없는 문자 집합 이름입니다


    Problem description
    ===================
    You receive the following error when (re)compiling or calling a piece of pl/sql:
    ORA-06550: line <num>, column <num>: ....
    or
    ORA-06552: PL/SQL: Compilation unit analysis terminated
    followed by
    ORA-06553: PLS-553: character set name is not recognized
  에러 원인 :  character set 이 섞여있음.

 

    캐릭터셋 확인 쿼리 :
    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);

    캐릭터셋 확인 쿼리 결과(잘못된 경우): varchar2가 2개 의 캐릭터셋이 설정되어있음.
        CHARACTERSET                            TYPES_USED_IN
        -----------------------------------------------------
        AL16UTF16                               NCHAR
        AL16UTF16                               NVARCHAR2
        AL16UTF16                               NCLOB
        US7ASCII                                CHAR
        US7ASCII                                VARCHAR2
        WE8DEC                                 VARCHAR2
        US7ASCII                                CLOB
       
       
    캐릭터셋 확인 쿼리 결과(정상인 경우): TYPES_USERD_IN 하나당 하나의 캐릭터셋
        CHARACTERSET                            TYPES_USED_IN
        -----------------------------------------------------
        AL16UTF16                               NCHAR
        AL16UTF16                               NVARCHAR2
        AL16UTF16                               NCLOB
        AL32UTF8                                CHAR
        AL32UTF8                                VARCHAR2
        AL32UTF8                                CLOB


    해결 방법 :

        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 수정한 경우 원복.


'DB' 카테고리의 다른 글

AUDIT - 감사  (0) 2010.07.30
SQL99 - Insert All, MERGE, IS NAN, INTERVAL  (0) 2010.07.30
Index-Organized Table(IOT), Partitioned Table & Index  (0) 2010.07.30
lock 관련  (0) 2009.02.27
Oracle 실행된 쿼리 보기  (0) 2009.02.12
Oracle Version 확인  (0) 2009.02.12
user/tablespace sql & export/import  (0) 2009.02.12
Oracle 10g install / client install / uninstall - CentOS 5.2  (0) 2009.02.12