ORACLE PDB(Pluggable DB) characterset 변경

ORACLE 12c 부터 CDB 환경을 사용한다면, CDB가 아닌 PDB로 서비스 하고 있을 가능성이 크다.
PDB의 characterset을 변경하는 것은 기존 CDB가 아닌 환경에서와 조금 다르며 하기 방법으로 가능하다

※ 개발용 테스트를 위해 작성하였으며 실 업무에서는 검토가 필요하다

---

• NLS_CHARACTERSET=AL16UTF8
• NLS_NCHAR_CHARACTERSET=AL16UTF16

UTF8에서 EUCKR  변경 진행, 데이터 없다는 전재하에 진행

• NLS_CHARACTERSET=KO16KSC5601
• NLS_NCHAR_CHARACTERSET=AL16UTF16

---

CDB 접속 및 PDB 확인

[oracle@56c517b8f964 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 5 02:00:46 2022

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

set pagesize 1000
set linesize 500
col name for a30
col open_mode for a10
col open_time for a50
SQL> select name, open_mode, open_time from v$pdbs;

NAME			       OPEN_MODE  OPEN_TIME
------------------------------ ---------- --------------------------------------------------
PDB$SEED		       READ ONLY  07-NOV-22 05.32.42.299 AM +00:00
ORCLPDB1		       READ WRITE 05-DEC-22 01.25.52.367 AM +00:00

PDB 접속

SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> show con_name  

CON_NAME
------------------------------
ORCLPDB1

PDB 중지

SQL> shutdown immediate
Pluggable Database closed.

set pagesize 1000
set linesize 500
col name for a30
col open_mode for a10
col open_time for a50
SQL> SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;
NAME			       OPEN_MODE  OPEN_TIME
------------------------------ ---------- --------------------------------------------------
ORCLPDB1		       READ WRITE 05-DEC-22 01.25.52.367 AM +00:00

restricted 활성 및 PDB open 진행

SQL> alter system enable restricted session;

System altered.

SQL> alter database open;

Database altered.

PDB characterset 변경 진행

set linesize 300
col name for a30
col value$ for a30      
SQL> select name, value$ from sys.props$ where name in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

NAME			       VALUE$
------------------------------ ------------------------------
NLS_NCHAR_CHARACTERSET	       AL16UTF16
NLS_CHARACTERSET	       AL16UTF8

SQL> alter database character set internal_use ko16ksc5601;

Database altered.

SQL> alter database character set ko16ksc5601;

Database altered.

set linesize 300
col name for a30
col value$ for a30      
SQL> select name, value$ from sys.props$ where name in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

NAME			       VALUE$
------------------------------ ------------------------------
NLS_NCHAR_CHARACTERSET	       AL16UTF16
NLS_CHARACTERSET	       KO16MSWIN949

---

ERROR

• ORA-12719: operation requires database is in RESTRICTED mode
  - alter system enable restricted session; 수행 후 진행
• ORA-00604: error occurred at recursive SQL level 1
  ORA-01109: database not open
  - alter database open; 수행 후 진행

---

위 처럼 PDB의 characterset을 변경하는 방법에 대해 알아 봤습니다.

ORACLE Multitenant Architecture CDB, PDB

---

---