OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.11.0.0
에러 : sqlplus 문자가 ???로 나올 때 해결 방법
윈도우 환경의 경우 아래 게시글 참조
https://positivemh.tistory.com/778
db를 구성 할 때 캐릭터셋은 KO16MSWIN949로 설정했지만
아래와 같이 Default languge와 Defalut territory를 Korean, Korea로 했을 경우
sqlplus 실행시 한글이 ???로 표시됨
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 21 22:49:00 2021
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
????
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7839 KING PRESIDENT 81/11/17 5000 10
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7566 JONES MANAGER 7839 81/04/02 2975 20
7902 FORD ANALYST 7566 81/12/03 3000 20
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7900 JAMES CLERK 7698 81/12/03 950 30
7934 MILLER CLERK 7782 82/01/23 1300 10
???
|
해결 방법 : os 유저로 NLS_LANG 설정
설치 시 어떤 캐릭터셋을 선택했는지 모르는 경우 nls_database_parameters 뷰 확인
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
SQL>
set lines 200 pages 1000
col PARAMETER for a30
col VALUE for a30
select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_RDBMS_VERSION 19.0.0.0.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET KO16MSWIN949
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
20 rows selected.
|
nls_database_parameters 뷰를 조회해서 나온 NLS_LANGUAGE, NLS_TERRITORY, NLS_CHARACTERSET 값을 아래처럼 입력하면됨
sqlplus 를 접속하는 유저인 oracle 계정으로 NLS_LANG 설정
1
2
3
4
5
|
# su - oracle
예시 $ export NLS_LANG="NLS_LANGUAGE"_"NLS_TERRITORY"."NLS_CHARACTERSET"
$ export NLS_LANG=american_america.ko16mswin949
$ echo $NLS_LANG
american_america.ko16mswin949
|
sqlplus 접속 후 확인
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 21 22:55:11 2021
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
|
???가 발생하지 않고 정상적으로 표시됨
원인 : 오라클 NLS 설정과 OS NLS_LANG 설정의 차이때문에 발생한 문제
db 생성 시 캐릭터셋을 ko16mswin949로 설정한 뒤 위와 같이 Default languge와 Defalut territory를 Korean, Korea로 설정한 경우 export 명령으로 NLS_LANG를 설정해주어야함
참조 : https://positivemh.tistory.com/778
'ORACLE > Trouble Shooting' 카테고리의 다른 글
[DBT-50000] Unable to check for available system memory. (0) | 2021.10.08 |
---|---|
ORA-00304: requested INSTANCE_NUMBER is busy (0) | 2021.09.27 |
PRCS-1046 (0) | 2021.08.21 |
AHF-00014: AHF Location ~/ahf/oracle.ahf is not owned by root in directory hierarchy (0) | 2021.08.15 |
ORA-02494: invalid or missing maximum file size in MAXSIZE clause (0) | 2021.08.14 |