[오라클]ORA-06553: PLS-553: character set name is not recognized

오라클 설치시 인스톨러에서 데이타베이스 자동 생성을 할 경우
OS 언어에 따라 자동으로 characterset 이 설정되어 설치되는데,
characterset을 임의로 변경하는 방법 입니다.

변경시나리오)

* 기존 개발용 오라클DB OLD_ORA 의 nls_characterset 은 US7ASCII 이다.

* 기존 클라이언트 nls_lang 은 AMERICAN_AMERICA.US7ASCII 이다.

* 새로운 한글windows OS에 오라클DB NEW_ORA 를 설치하였는데 nls_characterset 이 KO16MSWIN949 이다.

* OLD_ORA 의 데이타를 exp 유틸로 덤프를 떠서 NEW_ORA 에 imp 유틸로 복구하였는데,

일본및 중국 언어문자셋이 깨어져 버리고 ??? 등으로 바뀌어 유니크인덱스가 생성되지 않는다.

* 데이타 이전및 개발호환을 위해 nls_characterset 을 맞추기로 하였다.

새로 설치하지 않고 바꾸는 법 입니다.

절차 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> ALTER DATABASE CHARACTER SET US7ASCII;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

ALTER DATABASE CHARACTER SET US7ASCII 변경할때

큰집합이여야 된다는(superset) 등의 에러메세지 떨어질 경우
update SYS.PROPS$ set value$='US7ASCII' where name='NLS_CHARACTERSET';
shutdown immediate;
startup;

클라이언트 nls_characterset 셋팅 :
regedit
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
- NLS_LANG = AMERICAN_AMERICA.US7ASCII

[nls 환경보기]
--DB
SELECT * FROM nls_database_parameters
WHERE parameter='NLS_CHARACTERSET' OR parameter='NLS_LANGUAGE'

-- session
SELECT * FROM v$nls_parameters

===============================================================================================

위와 같이 설정후 OLD_ORA 데이타 덤프를 NEW_ORA 로 Import 시킨후 한글이 정상적으로 들어와 졌습니다.

그런데 SQLTools,토드 등으로 접속후 객체브라우져로 객체 리스트 보기에서 아래와 같은 에러가 발생하면서

ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-553: character set name is not recognized

객체리스트가 보이지 않았습니다.

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

쿼리)

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)

order by types_used_in;

결과)

CHARACTERSET TYPES_USED_IN
------------------------------ -------------
KO16MSWIN949 CHAR
US7ASCII CHAR
KO16MSWIN949 CLOB
US7ASCII CLOB
AL16UTF16 NCHAR
AL16UTF16 NCLOB
AL16UTF16 NVARCHAR2
KO16MSWIN949 VARCHAR2
US7ASCII VARCHAR2

CHAR,CLOB,VARCHAR2 에 중복된 CHARACTERSET이 존재함.

위와 같은 경우 다음과 같이 조취합니다.

조치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
------------------------------ -------------
US7ASCII CHAR
US7ASCII CLOB
AL16UTF16 NCHAR
AL16UTF16 NCLOB
AL16UTF16 NVARCHAR2
US7ASCII VARCHAR2

이후 SQLTools 나 토드접속시 에러가 나지 않고 객체리스트가 정상적으로 나오게 됩니다.