[Oracle] 캐릭터셋 변경

1. 현재 오라클에 설정된 캐릭터 셋 조회

SYS@orcl>select * from nls_database_parameters

  2  where parameter ='NLS_CHARACTERSET';

PARAMETER

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

VALUE

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

NLS_CHARACTERSET

AL32UTF8

SYS@orcl>select * from props$ where name='NLS_CHARACTERSET';

NAME

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

VALUE$

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

COMMENT$

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

NLS_CHARACTERSET

AL32UTF8

Character set

2. AL32UTF8 을 KO16MSWIN949 로 변경

① sysdba 권한으로 접속

SQL> sqlplus '/as sysdba'

② 시스템 정상 종료 및 마운트 접속

SYS@orcl>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@orcl>startup mount;

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size                  2066080 bytes

Variable Size             385878368 bytes

Database Buffers         1207959552 bytes

Redo Buffers               14708736 bytes

Database mounted.

③ 일반 사용자의 세션 가능 제한모드

SYS@orcl>alter system enable restricted session;

System altered.

④  job_queue_processes 에 등록된 JOB 프로세스 정지

SYS@orcl>alter system set job_queue_processes=0;

System altered.

⑤ aq_tm_processes 에 등록된 JOB 프로세스 정지

SYS@orcl>alter system set aq_tm_processes=0;

System altered.

⑥ DB 오픈

SYS@orcl>alter database open;

Database altered.

⑦ 변경할 캐릭터 셋으로 변경(한글 캐릭터 셋 : KO16MSWIN949 , KO16KSC5601)

SYS@orcl>alter database character set internal_use KO16MSWIN949;

Database altered.

⑧ DB 정상 종료

SYS@orcl>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

⑨ DB 재시작

SYS@orcl>startup

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size                  2066080 bytes

Variable Size             385878368 bytes

Database Buffers         1207959552 bytes

Redo Buffers               14708736 bytes

Database mounted.

Database opened.

3. 변경한 캐릭터 셋 확인

SYS@orcl>select * from nls_database_parameters

  2  where parameter='NLS_CHARACTERSET';

PARAMETER

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

VALUE

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

NLS_CHARACTERSET

KO16MSWIN949

SYS@orcl>select * from props$ where name='NLS_CHARACTERSET';

NAME

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

VALUE$

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

COMMENT$

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

NLS_CHARACTERSET

KO16MSWIN949

Character set

원본

http://zietz.tistory.com/86