문제) 오라클 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);
결과)
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> sqlplus /nolog
SQL> conn /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