개발은 너무해

characterset 변경 및 주의할 점. 본문

Oracle/Oracle Study

characterset 변경 및 주의할 점.

까칠 평생초보 2011.01.06 17:47
현재 캐릭터셋은 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로 변경하면 멀티바이트가 포함된 테이블도 정상적으로 작동이 된다.

'Oracle > Oracle Study' 카테고리의 다른 글

UNDO tablespace 변경  (0) 2011.07.26
MAXDATAFILES, DB_FILES(ORA-01118, ORA-00059)  (0) 2011.03.10
characterset 변경 및 주의할 점.  (0) 2011.01.06
오라클 10.2.0.1.0 설치  (0) 2010.09.08
Oracle Analytic Function(분석함수)  (0) 2010.05.24
리두 로그 삭제  (0) 2010.04.07
0 Comments
댓글쓰기 폼