오라클 CHARACTER 설정 확인 및 조치 사항

# 확인쿼리 1

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);

# 확인쿼리 2

SELECT
    PARAMETER
    , VALUE
FROM
    V$NLS_PARAMETERS;

AMERICAN_AMERICA.KO16KSC5601
KOREAN_KOREA.KO16KSC5601
KOREAN_KOREA.KO16MSWIN949

# 변경쿼리 1

update sys.props$ set value$='KOREAN_KOREA.KO16MSWIN949' where name='NLS_LANG';
update sys.props$ set value$='KOREAN_KOREA.UTF8' where name='NLS_LANGUAGE';
update sys.props$ set value$='KOREAN_KOREA.KO16MSWIN949' where name='NLS_LANGUAGE';

# 변경쿼리 2

update sys.props$ set value$='KO16MSWIN949' where name='NLS_CHARACTERSET';
update sys.props$ set value$='KO16MSWIN949' where name='NLS_NCHAR_CHARACTERSET';
update sys.props$ set value$='KOREAN_KOREA.KO16MSWIN949' where name='NLS_LANGUAGE';


---------------------------------------------------------------------------------------------



ORA-06553: PLS-553: character set name is not recognized ORACLE  
2007/08/07 17:19

http://blog.naver.com/maenong/60040796234
이 포스트를 보낸곳 ()


참고문서
ttp://www.oracle.com/technology/global/kr/pub/columns/oracle_lns_1.html#mozTocId550886
http://www.dbguide.net/dbqa/dbqa111003.jsp?catenum=26&page=1&idx=7866



문제) 오라클 10g : export 실행할때나 토드로 접속할때 아래의 에러발생.
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-553: character set name is not recognized


조치1) sysdba로 로긴하여 아래 구문 실행

SQL> @ $ORACLE_HOME/javavm/install/rmjvm.sql
SQL> @ $ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> @ $ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @ $ORACLE_HOME/rdbms/admin/catproc.sql

==> 문제 해결 안됨.



원인) 아래 쿼리를 날렸을때 데이터 타입이 중복되어서 나옴

쿼리)

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);
[출처] ORA-06553: PLS-553: character set name is not recognized|작성자 뉴타입


결과)

CHARACTERSET  TYPES_USED_IN
----------------- -------------
WE8ISO8859P1      VARCHAR2
KO16KSC5601        VARCHAR2
KO16KSC5601        CHAR
WE8ISO8859P1       CHAR
AL16UTF16            NCLOB
KO16KSC5601        CLOB
AL16UTF16            NVARCHAR2
KO16KSC5601        NVARCHAR2
WE8ISO8859P1       CLOB
AL16UTF16            NCHAR



조치2) sysdba로 아래 구문들 실행

주의사항)

a) Make sure the parallel_server parameter in INIT.ORA is set to false or it is not set at all.
b) Run the next script in SQLPLUS connected "as sysdba"



SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> COL VALUE NEW_VALUE CHARSET
        SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
SQL> COL VALUE NEW_VALUE NCHARSET
        SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
SQL> ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
-- yes, 2 times startup/shutdown . This is not a typo
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;



실행중 오류발생)

ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE KO16KSC5601
==> ORA-12714: invalid national character set specified

원인)

NATIONAL CHARACTER SET 은 Oracle9i 이후에는 UTF8과 AL16UTF16 만 제공
확인)  

SELECT VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER in ('NLS_NCHAR_CHARACTERSET','NLS_CHARACTERSET')
결과)

PARAMETER                                  VALUE
---------------------------------- ---------------
NLS_CHARACTERSET                     KO16KSC5601

NLS_NCHAR_CHARACTERSET         KO16KSC5601

조치)

update sys.props$ set value$='AL16UTF16' where name='NLS_NCHAR_CHARACTERSET';




이후 위의 조치2)를 다시실행하면 아래와 같은 결과를 얻을수 있다..



문제해결)

CHARACTERSET   TYPES_USED_IN
----------------- -------------
KO16KSC5601        VARCHAR2
KO16KSC5601        CHAR
KO16KSC5601        CLOB
AL16UTF16            NCLOB
AL16UTF16            NVARCHAR2
AL16UTF16            NCHAR