Oracle Character Set 변경

⚠️ 캐릭터셋 변경은 DB가 깨질 수 있음 — 사전 백업 필수.

확인

SELECT * FROM nls_database_parameters WHERE parameter LIKE '%CHARACTERSET%';
SELECT * FROM sys.props$ WHERE name='NLS_CHARACTERSET';

클라이언트 NLS_LANG

export NLS_LANG=KOREAN_KOREA.UTF-8
# 또는
export NLS_LANG=KOREAN_KOREA.KO16MSWIN949

DB 캐릭터셋 변경

Import 시 불일치(ORA-01461, IMP-00038 등)면 원본과 같은 셋으로 임시 맞출 때:

CONN / AS SYSDBA;
UPDATE sys.props$ SET value$='KO16KSC5601' WHERE name='NLS_CHARACTERSET';
UPDATE sys.props$ SET value$='KO16KSC5601' WHERE name='NLS_NCHAR_CHARACTERSET';
COMMIT;
SHUTDOWN IMMEDIATE;
STARTUP;

AL32UTF8

UPDATE sys.props$ SET value$='AL32UTF8' WHERE name='NLS_CHARACTERSET';
UPDATE sys.props$ SET value$='AL32UTF8' WHERE name='NLS_NCHAR_CHARACTERSET';
UPDATE sys.props$ SET value$='KOREAN_KOREA.AL32UTF8' WHERE name='NLS_LANGUAGE';
COMMIT;

KO16MSWIN949

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';
COMMIT;

재시작 후 명시 적용

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;
ALTER DATABASE CHARACTER SET AL32UTF8;   -- 또는 KO16MSWIN949
SHUTDOWN IMMEDIATE;
STARTUP;