characterset 변경 및 주의할 점.

현재 캐릭터셋은 KO16MSWIN949 이다.

그래서 뷁 또는 맗 같은 멀티바이트가 insert 할 수가 있다.

C:\Windows\System32>sqlplus system/******@prod

SQL*Plus: Release 10.2.0.1.0 - Production on 목 1월 6 17:09:02 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select * from sys.props$ where name like 'NLS_CH%';

NAME

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

VALUE$

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

COMMENT$

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

NLS_CHARACTERSET

KO16MSWIN949

Character set

SQL> conn scott/tiger@prod

Connected.

SQL> insert into dept values(70,'뷁','맗');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

        70 뷁             맗 

5 rows selected.

SQL> conn /as sysdba

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1220868 bytes

Variable Size             146804476 bytes

Database Buffers          461373440 bytes

Redo Buffers                2969600 bytes

Database mounted.

SQL>

SQL> alter system enable restricted session;

System altered.

SQL> alter database open;

Database altered.

SQL> alter database character set internal_use KO16KSC5601;

Database altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1220868 bytes

Variable Size             146804476 bytes

Database Buffers          461373440 bytes

Redo Buffers                2969600 bytes

Database mounted.

Database opened.

SQL> select * from sys.props$ where name like 'NLS_CH%';

NAME

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

VALUE$

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

COMMENT$

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

NLS_CHARACTERSET

KO16KSC5601   -- 변경완료

Character set

SQL> conn scott/tiger@prod

Connected.

SQL> select * from dept;

ERROR:

ORA-29275: partial multibyte character    :  멀티바이트가 포함된 테이블은 에러를 발생시킨다.

no rows selected

SQL> 

다시 위와 같은 방법으로 KO16MSWIN949로 변경하면 멀티바이트가 포함된 테이블도 정상적으로 작동이 된다.