[Oracle] 언어셋 변경 NLS_LANGUAGE

Oracle NLS_LANGUAGE 변경 Scripts

• 데이터 베이스 NLS_LANGUAGE 변경 방법에 대해 공유 합니다. 
• 독일어로 변경하는 방법에 대해 알아 보겠습니다. 

-- dba 접속
sqlplus sys/oracle as sysdba

-- 언어셋 변경 
update sys.props$ set value$='AL32UTF8' where name='NLS_CHARACTERSET';  
update sys.props$ set value$='AL16UTF16' where  name='NLS_NCHAR_CHARACTERSET';
update sys.props$ set value$='GERMAN_GERMANY.WE8ISO8859P1  ' where name='NLS_LANGUAGE';
commit;

- db 재기동
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 INTERNAL_USE WE8ISO8859P1;

SHUTDOWN IMMEDIATE;

STARTUP;

 

NLS_LANGUAGE 변경 진행 과정

[oracle@153467d4adce ~]$ sqlplus sys/oracle@orclcdb  as sysdba  

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 13 10:04:38 2022
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> update sys.props$ set value$='AL32UTF8' where name='NLS_CHARACTERSET';  

1 row updated.

SQL> update sys.props$ set value$='AL16UTF16' where  name='NLS_NCHAR_CHARACTERSET';

1 row updated.

SQL> update sys.props$ set value$='GERMAN_GERMANY.WE8ISO8859P1  ' where name='NLS_LANGUAGE';

1 row updated.

SQL> commit;

Commit complete.

SQL> SHUTDOWN IMMEDIATE;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> SQL> 
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1610609928 bytes
Fixed Size                  9135368 bytes
Variable Size             419430400 bytes
Database Buffers         1174405120 bytes
Redo Buffers                7639040 bytes
Database mounted.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8ISO8859P1;

Database altered.

SQL> SHUTDOWN IMMEDIATE;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> SQL> STARTUP;
ORACLE instance started.

Total System Global Area 1610609928 bytes
Fixed Size                  9135368 bytes
Variable Size             419430400 bytes
Database Buffers         1174405120 bytes
Redo Buffers                7639040 bytes
Database mounted.

Database opened.

 

변경된 NLS 정보 확인 방법

set linesize 400
set pagesize 100
col PARAMETER for a30
col VALUE for a100

-- 현재 nls 구성정보
SELECT * FROM V$NLS_PARAMETERS;
 
--설치시 nls 정보
SELECT * FROM NLS_DATABASE_PARAMETERS;
 
--인스턴스 nls 정보
SELECT * FROM NLS_INSTANCE_PARAMETERS;​