개발자 여행기

Oracle 19C Procedure에서 Where 조건에 In 값을 넘겨야 하는데,
문자열을 만들어서 넘기니 원하는 결과 값이 나오지 않았다.
알고 보니, 받는 부분에서 처리를 해줘야 된다는 것을 알았다.
이럴 경우, 어떻게 처리해야 되는지 알아보도록 하자.

■ Where 조건에 In 값 넘기는 방법

1. 아래의 Procedure를 만들었다고 가정했을 때, In에 넘길 파라미터 값을 바로 사용하지 않고 아래와 같이 변경하면 된다.

2. PARAM에 넘길 값은 '11', '22' 이런 식으로 넘기는 것이 아니라 11, 22로 넘기면 원하는 결과 값을 받을 수 있다.

  - 싱글쿼터(') 값은 프로시저 안에서 처리하고 있다.

■ 결론

1. PARAM이라는 파라미터는 하나의 문자열로 취급하므로, '11', '22'를 넘기더라도 '11, 22'로 인식하기 때문에 올바른 값이 나오지 않는다.

■ HeidiSQL Download

1. https://www.heidisql.com/download.php 사이트에 접속한다. 또는 검색어를 통하여 사이트로 진입한다.

2. Installer, 32/64 bit combined를 클릭하여 다운로드한다.

■ HeidiSQL 설치 방법

1. 다운로드한 파일을 클릭하고, 아래 이미지와 같이 순서대로 실행하면, 최종적으로 서버에 접속할 수 있는 창이 뜬다.

2. 접속할 수 있는 창이 뜨면 호스트명/IP, 사용자, 암호, 포트, 데이터베이스를 입력한다.

    세션이름은 마우스 우클릭을 누르면 이름을 변경할 수 있다.

3. 올바르게 입력하면 아래 이미지와 같이 이제 쿼리를 실행할 수 있는 화면이 뜬다. (완료)

Oracle 서버 설치, 추가적으로 클라이언트까지 설치하고, DB를 Import 하기 위해서 SQLPLUS를 접속하였으나,
ORA-12560: TNS:프로토콜 어댑터 오류가 났다.
열심히 구글링을 하였으나, 리스너를 재시작하라는 말뿐, 리스너는 정상적으로 잘 작동되고 있었다.
이럴 경우, 확인해야 할 사항이 있는데 지금부터 알아보도록 하자.

■ Listener가 정상적으로 작동하고 있을 때 확인해야 할 사항

1. 명령 프롬프트를 실행하여, sqlplus를 접속한다.

2. ORA-12560: TNS:프로토콜 어댑터 오류

  - 원래 이 오류는 Listener가 정상적으로 작동하고 있지 않을 때 나온다고 한다.

3. 오라클 서버, 클라이언트를 모두 설치하였기 때문에, sqlplus가 서버 sqlplus로 실행되는지, 클라이언트 것으로 실행되는지 확인해야 한다.

    파일을 검색해 보면 아래와 같이 2개가 있는 것을 확인할 수 있다. (위의 것이 클라이언트, 아래의 것은 서버)

4. sqlplus로 실행해 보면, 서버는 정상적으로 접속이 되고, 클라이언트는 처음 이미지와 같이 오류가 발생하는 것을 확인할 수 있다.

5. 그럼 이제 명령 프롬프트로 실행했을 때, 서버 sqlplus로 접속되게 하는 방법은 환경변수를 수정하는 것이다.

    시작에서 시스템 환경 변수 편집 메뉴로 들어간다.

6. 시스템 속성 - 고급 - 환경변수 버튼을 클릭한다.

7. 시스템 변수에서 편집 버튼을 클릭한다.

8. 환경변수 순서를 보면 클라이언트가 서버보다 위에 위치한 것을 볼 수 있다.

    서버를 클라이언트 위로 이동한다.

9. 저장 후, 다시 명령프롬프트로 sqlplus를 실행해 보면, 접속이 되는 것을 확인할 수 있다.

■ Oracle 설치 후 Port 변경 방법

1. Oracle이 설치된 경로에서 listener.ora, sqlnet.ora 파일이 있는 곳으로 이동한다.

  - 경로 예) D:\app\test\virtual\product\12.2.0\dbhome_1\network\admin

2. listener.ora 파일에서 아래 이미지 표시된 부분에 변경할 Port번호를 입력한다.

3. 마찬가지로 sqlnet.ora 파일에서 아래 이미지 표시된 부분에 변경할 Port번호를 입력한다.

4. 2개의 파일을 저장하고, 윈도우 - 시작에서 서비스로 들어간다.

5. Listener 서비스를 중지하고, OracleService... 를 재시작한다.

    재시작이 완료되었으면, Listener 서비스를 재시작한다.

    여기까지 완료되었으면, 변경된 포트번호로 접속이 가능하다.

    접속이 안되면 혹시 tnsnames.ora에도 변경했는지 확인한다.

■ PL/SQL Developer 설치 후 가장 먼저 하는 설정

1. Configure - Preferences - User Interface - Fonts로 들어가서 Fonts 설정

  - Browser 설정 시, 아래 이미지와 같이 설정 값이 변경된다.

  - Grid 설정 시, 아래 이미지와 같이 설정 값이 변경된다.

  - Editor 설정 시, 아래 이미지와 같이 설정 값이 변경된다.

2. Configure - Preferences - User Interface - Editor로 들어가서 Syntax Highlighting 설정

  - Keywords : select, from, where

  - Comment : 주석

  - String : ''(싱글쿼터) 안에 쓰이는 문자

  - Numbers : ''(싱글쿼터)가 없는 숫자

  - Symbols : 조건(=) 등의 문자

3. Configure - Preferences - User Interface - Editor로 들어가서 Keyword case 설정

  - Keyword 입력 시 대문자로 자동 변환할지 설정 (이미지는 2번 참고)

4. Configure - Preferences - User Interface - Code Assistant로 들어가서 Delay (ms) 설정

  - table을 alias로 지정하고, 칼럼 Assistant 이용 시 Delay 속도, 숫자가 낮을수록 빠르게 이용할 수 있다.

5. Configure - Preferences - Oracle - Output로 들어가서 Save to file 설정

  - 아무 경로에 .txt파일을 만들고, 만든 파일을 지정한다. 이렇게 지정해 두면, DBMS_OUTPUT.PUT_LINE() 내용을 .txt파일에서 확인할 수 있다.

6. Configure - Preferences - Oracle - Logon History로 들어가서 Store with password 체크

  - 로그인할 때, 비밀번호를 항상 입력해야 하는데 체크를 하면 비밀번호를 입력하지 않고 로그인할 수 있다.

  - 단, 체크를 한다고 바로 적용되는 것은 아니며, 프로그램을 종료했다가 비밀번호를 입력하고 로그인을 다시 한번 해야 한다. (이때 저장됨)

  - 저장된 이후 다시 한번 실행하면 아래 이미지와 같이 자동으로 바로 로그인은 되지 않는다.

    Username 오른쪽에 ...을 클릭하고, 접속해야 하는 곳을 클릭하면 비밀번호를 입력 안 하고 로그인할 수 있다.

7. Configure - Preferences - Window Types - SQL Window로 들어가서 AutoSelect statement, Show gutter (line numbers) 체크

  - Default 값으로 사용할 시, Select 쿼리가 2개에 세미콜론(;)으로 분리되어 있을 때, 실행하면 쿼리 2개가 모두 실행되지만,

    체크를 하게 되면 현재 커서가 있는 Select 쿼리만 실행한다.

  - Show gutter를 체크하면 줄 번호를 볼 수 있다.

Oracle 서버를 설치 후, 쿼리툴(DBeaver, PL/SQL Developer 등)을 이용하여 접속까지 되는 것을 확인하였다.
이후 C#에서 DB를 접속하였으나,
[ORA-01017: 사용자명/비밀번호가 부적합, 로그온 할 수 없습니다.]
라는 메시지가 나오면서 접속이 되지 않았다.
해결책을 찾아보도록 하자.

■ C#에서 DB접속 시, ORA-01017 에러 해결책

1. 이전에 포스팅했던 내용을 일단 먼저 숙지한다.

  - ORA-01017: 사용자명/비밀번호가 부적합, 로그온 할 수 없습니다.

2. 여기서 순서가 중요하다. 포스팅 내용대로 해도 안된다고 하면, 대소문자 구분 안 하게 설정값을 다시 변경한다.

3. 그리고 비밀번호를 다시 설정한다. (꼭 비밀번호를 다른 것으로 설정 안 해도 된다. 즉 비밀번호를 동일한 것으로 다시 해도 된다는 소리)

4. C#에서 접속이 되는지 확인한다. - 해결되었음

결론: sec_case_sensitive_logon = TRUE (대소문자 구별함)에서 비밀번호를 abcde로 설정했더라도, 비밀번호가 다른 게 인식된다는 것을 확인

DBeaver를 설치하면 여러 환경이 기본값으로 설정되어 있다.
SQL 편집기 글씨도 작고, 폰트도 마음에 안 들고 해서 설치 후 몇 가지 내가 설정하는 것을 정리해 보았다.

■ SQL 편집기 글꼴 및 크기 변경

1. [상단메뉴 - 윈도우 - 설정]으로 이동한다.

2. [User Interface - 모양 - 색상 및 글꼴]로 이동하여 [DBeaver Fonts - Monospace font]로 가서 원하는 글꼴 및 크기로 편집한다.

■ 쿼리 작성 중 테이블 자동완성 목록에서 클릭하면 테이블 옆 Space와 Alias 자동 생성 막는 방법

1. [편집기 - SQL 편집기 - SQL 자동완성]으로 이동한다.

2. [Insert space after table/column names]는 체크를 해제한다. (자동완성 중 테이블 및 컬럼을 클릭하면 자동으로 Space가 들어가는 기능)

3. [Insert table aliases (in FROM clause):]는 N/A로 변경한다. (자동완성 중 테이블을 클릭하면 자동으로 Alias가 생성되는 기능)

■ 쿼리 작성 중 싱글쿼터('), 더블쿼터(") 작성 시 자동으로 닫히는 기능 막는 방법

1. [편집기 - SQL 편집기 - Code Editor]으로 이동한다.

2. Auto close의 내용을 보고 체크를 해제한다.

■ 줄 바꿈이 공백인 경우 쿼리 실행이 될 수 있도록 변경하는 방법

1. 아래와 같이 쿼리문에 줄 바꿈 공백이 들어간 경우, 기본설정에서는 에러가 난다.

2. 이를 무시하려면 [편집기 - SQL 편집기 - SQL 실행]으로 이동한다.

3. [Blank line is statement delimiter]는 체크를 해제한다.

■ 행 번호 표시

1. [편집기 - 문서 편집기]으로 이동한다.

2. 행 번호 표시를 체크한다.

■ 단축키 설정

1. 단축키를 변경은 [User Interface - 키]로 들어가서 변경할 수 있다.

2. 단축키 변경은 바꾸고 싶은 명령을 클릭한 후, 하단 바인딩에서 사용할 단축키를 누르면 세팅된다.

DBeaver 설치 후, 쿼리를 실행하였는데, 테이블이 없다고 메시지가 나왔다.
알고 보니 스키마 지정이 안 돼서, 테이블 앞에 스키마 이름까지 써야 쿼리가 된다는 것을 알았다.
스키마 이름까지 쓰는 건 번거로워 스키마 이름을 안 쓰는 방법을 알아보도록 하자.

■ DBeaver 실행 시,  Default Schema 설정하는 방법 1

1. 아래 이미지와 같이 Schema에서 마우스 우클릭 후, Set as default를 클릭한다. (완료)

■ DBeaver 실행 시,  Default Schema 설정하는 방법 2

1. 아래 이미지와 같이 Database에서 마우스 우클릭한다.

2. Edit Connection을 클릭한다.

3. Connection settings - Initialzation 클릭 후, Default schema에서 기본값으로 지정할 스키마를 선택하고 저장한다. (완료)

어느 날 잘 되고 있던 Oracle Database가 갑자기 접속이 안 되는 현상이 발생하였다.
Error Code는 ORA-01034, ORA-27101
구글링을 하여도 거의 서비스를 재시작하라는 거밖에 없었다.
모든 게 정상적으로 서비스가 올라와 있었는데 접속이 되지 않는 경우, 어떻게 해결하는지 알아보도록 하자.

■ ORA-01034, ORA-27101 에러 해결책

1. 잘 사용하고 있던 Database가 갑자기 아래 이미지와 같이 ORA-01034, ORA-27101 에러코드가 발생하면서 접속이 되지 않았다.

2. 명령 프롬프트 - sqlplus를 실행하여 sysdba로 접속한다.

>> sqlplus / as sysdba

 

-- 정상적으로 접속되는 경우 아래 이미지와 같이 SQL로 진입한다.

-- 접속이 되지 않는 경우 아래 이미지와 같이 ORA-12560 에러코드가 발생

3. SQL로 진입하지 못했을 경우, 명령 프롬프트에서 아래와 같이 먼저 Database를 직접 지정한다.

4. 그리고 다시 명령 프롬프트에서 sqlplus / as sysdba로 다시 접속하면 SQL로 진입된다.

    단, Connected to an idle instance.라는 메시지가 나온다. (휴지 인스턴스(idle instance)는 데이터베이스가 셧다운 된 상태)

5. 아래의 쿼리를 실행하면, 아래 이미지와 같이 에러가 발생한다. 

SQL> SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;    -- 현재 접속해 있는 Database와 상태를 확인할 수 있는 쿼리

6. startup을 실행한다. 이미 실행 중이라는 메시지가 나오면, shtdown immediate를 하고 다시 startup 한다.

    아래와 같이 에러가 발생한다. (정상적인 서버도 아래 경로에 xxx.ORA파일은 없음)

SQL> startup;

7. [C:\app\aa\virtual\admin\bb\pfile]의 경로로 들어가면 [init.ora. 숫자]로 된 파일이 있는데, 이 파일을 복사하여

    [C:\app\aa\virtual\product\12.2.0\dbhome_1\database] 경로 안으로 붙여 넣고 파일 이름을 [initbb.ora]로 변경한다.

    (주의 : 빨간색 글씨는 Database명으로, 해당 Database를 선택하면 된다. 경로는 사용자 PC에 따라 달라진다.)

8. SQL에서 startup을 파라미터와 함께 실행한다.

SQL> startup pfile="C:\app\aa\virtual\product\12.2.0\dbhome_1\database\initbb.ora"

 

-- 아래 이미지와 같이 성공적으로 startup이 된다.

9. 5번의 쿼리를 다시 실행한다. 아까와 달리 조회가 되는 것을 볼 수 있다.

SQL> SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;    -- 현재 접속해 있는 Database와 상태를 확인할 수 있는 쿼리

10. 마지막으로 DBeaver에서 Connection Test 해봐도 접속이 잘되는 것을 볼 수 있다.

※ 주의 : 이렇게 서비스가 시작된 것은 윈도우 서비스에서 재시작을 하면, 위와 같이 에러가 똑같이 나기 때문에 startup 할 때, pfile 파라미터를 넘

              겨줘서 서비스를 재시작해야 한다.

Oracle DB Dump는 [expdp, impdp] 방법과, [exp, imp] 이렇게 2가지로 크게 요약된다.
이번 시간에는 [exp, imp]를 이용하여, 전체 데이터를 export, import를 해보자.

■ exp를 이용하여 데이터 export

1. 명령 프롬프트(cmd)를 실행하여, 아래 명령어를 입력한다.

>> exp userid=system/1@database file='C:\dump.dmp' full=y

 

-- system : 로그인 ID (고정 값이 아니고, 현재 사용하고 있는 ID를 입력하면 됨)

-- 1 : 로그인 Password (고정 값이 아니고, 현재 사용하고 있는 ID에 맞는 Password를 입력하면 됨)

-- database : export 할 database (옵션 값으로 안 써도 됨. database가 여러 개 있을 때 지정 - export 할 database를 입력하면 됨)

-- file : export 할 파일 경로 및 파일명

-- full = y : database를 전체 export (옵션 값)

2. export가 완료되면 위에서 지정한 경로에 dmp파일이 생성된다.

    (파일이 3개인 이유는 과거에 export를 해서 생긴 파일이므로 별 다른 의미는 없음)

■ imp를 이용하여 데이터 import

1. 명령 프롬프트(cmd)를 실행하여, 아래 명령어를 입력한다.

>> imp userid=system/1@database file='C:\dump.dmp' full=y

 

-- system : 로그인 ID (고정 값이 아니고, 현재 사용하고 있는 ID를 입력하면 됨, import대상 ID)

-- 1 : 로그인 Password (고정 값이 아니고, 현재 사용하고 있는 ID에 맞는 Password를 입력하면 됨)

-- database : import 할 database (옵션 값으로 안 써도 됨. database가 여러 개 있을 때 지정 - import 할 database를 입력하면 됨)

-- file : import 할 파일명

-- full = y : database를 전체 export (옵션 값)

2. import가 완료되면 해당 database를 확인해 본다. import가 잘 되어있는 것을 확인할 수 있다.

■ 주의할 점

1. NLS_LANG을 export 한 것과 맞춰서 import 해야 한다.

2. NLS_LANG을 맞추지 않을 경우 아래 이미지 같이 주석 문자가 깨지는 현상이 발생하였다. (다시 import 함)

3. NLS_LANG을 을 맞추고 다시 Import하니 주석도 잘 나오는 것을 확인하였다.

※ 참고로 문자 관련해서는 아래 포스팅도 같이 참고하면 좋을 거 같다.

2024.01.27 - [Oracle] - ORA-12721, ORA-06552, ORA-06553 해결 방법 및 주의 사항

DBeaver로 DB에 잘 접속하고, 쿼리를 실행하였더니, No active connection이라는 메시지가 나왔다.
다행히 해결책은 간단하였다.
해결책을 알아보도록 하자.

■ DBeaver No active connection 에러 해결

1. 아래 이미지는 DBeaver로 Connection을 했을 때의 모습이다.

2. 접속한 이후, 쿼리를 실행하면 다음과 같이 No active connection이라는 메시지가 나온다.

3. 해결책은 다행히도 간단하다. 아래 이미지 상단에 빨간 박스에 Database를 지정하면 된다.

4. 화살표 버튼을 클릭하면, 아래 이미지와 같이 Database를 선택할 수 있는 창이 나타난다.

5. Database를 지정하고 쿼리를 다시 실행하면, 잘 실행되는 것을 볼 수 있다.

오라클 설치 후, DBeaver를 통하여 접속하려고 하였으나,
[ORA-12638 신용 검색에 실패했습니다.]라는 에러가 발생하였다.
해결책은 의외로 간단하였다.

■ 해결책

1. 오라클이 설치되어 있는 경로를 찾는다. 대략 아래와 비슷하다.

C:\Oracle\product\12.2.0\dbhome_1\network\admin

2. 위의 경로로 들어가면 sqlnet.ora라는 파일이 있다.

3. 파일을 열어 아래 이미지 사각형으로 표시된 부분 앞에 #을 붙여주고 저장하면 DBeaver가 잘 접속되는 것을 확인할 수 있다.

4. 바로 저장이 안 되면 다른 경로로 저장한 후 파일을 덮어쓰기 하도록 하자.

# 원인 : 오라클 설치 시 관리자 권한으로 설치하지 않아 생기는 문제

오라클 설치 후, 데이터 Import까지 하여 잘 사용하고 있었으나, 문자열을 아스키코드로 변환하는 과정에서,
과거 오라클을 설치했을 때와 다른 아스키코드 값이 나와 확인해 보니,
Characterset이 다르게 설정되었다는 것을 알게 되었다.
Characterset을 변경하는 방법 및 변경 과정에서 ORA-12721 에러가 발생하였는데,
이 모든 것들의 해결법을 알아보도록 하자.
이 작업을 하기 전에 데이터 Backup을 받아 놓도록 하자.
(Characterset을 바꾸면 이미 저장되어 있는 한글 데이터는 깨질 수 있다.)

■ 쿼리를 통하여 아스키 값 확인

-- 아래 쿼리 실행을 통하여, 과거 설치된 버전과 현재 설치된 버전 아스키 값 확인

select ascii('가나다')

 from dual

■ 아스키 값이 다르게 나오는 경우 먼저 Characterset을 확인

-- 아래 쿼리를 실행

select *

 from sys.props$

where name in ('NLS_NCHAR_CHARACTERSET', 'NLS_CHARACTERSET')

 

-- 실행 결과

-- 이전에 설치했던 Characterset 확인

-- NLS_CHARACTERSET이 다르다.

■ Characterset 변경

1. 명령 프롬프트(cmd) 창 실행하여 아래 구문을 차례대로 실행한다.

2. sqlplus / as sysdba

3. update props$ set value$='KO16MSWIN949' where name='NLS_CHARACTERSET';

4. commit;

5. Characterset을 다시 확인하면, 아래 이미지와 바뀐 것을 볼 수 있다.

6. 아스키 값을 확인했던 쿼리를 다시 실행해 보면 아직 바뀌지 않은 것을 볼 수 있다. 서비스로 들어가서 아래 표시된 부분을 재시작한다.

    (sqlplus에서 shutdown immediate; startup;으로도 가능)

    그러면 아스키 값이 변경된 것을 확인할 수 있다.

7. 이후, 문제는 SQL편집 툴에서 테이블 목록을 열면 아래와 같은 메시지가 발생한다.

    ORA-06552: PL/SQL: Compilation unit analysis terminated

    ORA-06553: PLS-553: 알 수 없는 문자 집합 이름입니다

8. 아래 쿼리를 실행하여 몇 가지를 확인해 보자.

-- 아래 쿼리를 실행

select distinct

           nls_charset_name(charsetid) characterset,

           decode(type#, 1, decode(charsetform, 1, 'varchar2',

                                                                           2, 'nvarchar2',

                                                                           'unknown'),

                                    9, decode(charsetform, 1, 'varchar',

                                                                            2, 'nchar varying',

                                                                           'unknown'),

                                    96, decode(charsetform, 1, 'char',

                                                                              2, 'nchar',

                                                                             'unknown'),

                                    112, decode(charsetform, 1, 'clob',

                                                                                2, 'nclob',

                                                                               'unknown')

                       ) types_used_in

  from sys.col$

where charsetform in (1, 2) and type# in (1, 9, 96, 112)

order by characterset

 

-- 실행 결과 (이쪽에는 Characterset이 바뀌지 않았음)

■ ORA-06552, ORA-06553 해결방법

1. sqlplus로 접속하여, 아래 순서대로 실행한다.

2. shutdown immediate;

3. startup mount;

4. ALTER SYSTEM ENABLE RESTRICTED SESSION;

5. ALTER SYSTEM SET AQ_TM_PROCESSES = 0;

6. ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0;

7. ALTER DATABASE OPEN;

8. col value new_value charset

9. SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

10. ALTER DATABASE CHARACTER SET INTERNAL_USE & CHARSET;

11. shutdown immediate;

12. startup;

13. 여기까지 작업을 진행하면 에러를 해결할 수 있다.

※ 10번에서 [ORA-12721 다른 세션이 활성일 때는 실행할 수 없는 작업] 에러가 나오는 경우 해결책

  - 명령 프롬프트를 관리자 권한으로 실행하여 다시 진행한다. (여기까지만 해서 해결됨)

  - 그래도 안 되는 경우 shutdown immediate; startup; 진행 후, 다시 명령 프롬프트를 관리자 권한으로 실행하여 다시 진행해 본다.

■ 마지막으로 데이터 확인

1. Characterset을 변경하기 전 한글 데이터가 이미 존재하는 경우, 한글이 깨지는지 확인한다.

2. 한글이 깨지면, Import를 다시 한다. (이 방법 말고, 해결책이 있으면 댓글 달아주세요.)

■ DBeaver 인증 에러

1. DBeaver 설치 후 오라클 서버에 접속을 하려고 하니 다음과 같은 에러가 발생하였다.

2. 번역내용

■ 해결책

1. 위의 에러 내용을 자세히 보면 해결책이 나와 있다.

2. [윈도우 - 설정 - 연결]로 들어가서 Security의 Use Windows trust store 체크를 해제한다.

3. 적용 후 DBeaver를 재실행한다. 그러면 오라클 서버에 잘 접속되는 것을 확인할 수 있다.

오라클 서버로 붙지 못하여, 이런저런 작업을 하면서 shutdown immediate, startup을 반복하는 중
아래와 같은 에러메시지를 만났다.
해결책은 의외로 간단했다.

■ shutdown immediate 에러

1. 윈도우 서비스에서 재시작이 되지 않아 sqlplus를 접속하여 shutdown immediate을 시켜봤는데 다음과 같은 에러가 발생하였다.

   - sqlplus / as sysdba로 접속

   - shutdown immediate

2. cmd창을 닫고 재실행 후 sqlplus 재접속, shudown immediate를 실행하면 정상적으로 실행된다.

    원인은 데이터베이스가 정상적으로 종료되었을 때 발생되는 문제라고 한다.

오라클 서버를 설치하고, DBeaver를 통하여 서버에 접속을 시도하는 중
ORA-12514: 데이터베이스에 접속할 수 없습니다. 서비스가 리스너에 등록되지 않았습니다.
라는 메시지와 함께 접속되지 않았다.
해결책을 알아보도록 하자.

■ 접속에러

- 오라클을 설치하고 설치할 때의 정보를 아래와 같이 입력하였다. Test Connection을 누르니 아래와 같은 메시지가 발생하였다.

■ 해결책

1. 오라클을 설치한 경로로 이동한다. 설치 경로에 따라 다르겠지만 기본으로 설치된 경로는 아래 경로와 비슷한 형태일 것이다.

    (C:\app\belli\virtual\product\12.2.0\dbhome_1\network\admin)

2. 위와 같은 경로로 들어가서 listener.ora 파일을 클릭한다.

3. SID_LIST_LISTENER 하단 빨간색 박스에 오라클 서버를 설치할 때의 Database 이름을 적어 넣고 저장한다.

4. 혹시 설치할 때의 Database의 이름이 무엇인지 모르면 아래의 이미지를 보고 설치할 때의 기억을 되살려야 한다.

5. 저장하였으면 다시 접속을 시도해 본다.

6. 접속이 되지 않으면 서비스에 가서 오라클 리스너를 재실행해본다. 그러면 접속되는 것을 볼 수 있을 것이다.

    (혹시라도 접속이 안 되는 경우 아래 OracleService부터 재실행해보자.)

■ Pivot - 행을 열로 변환

■ UnPivot

■ 사용하면서 유용했던 단축키 정리

단축키	설명	비고
Shift + Alt + A	새로 블록 편집/해제	단축키를 누르면 십자가 표시로 변경된다. 블록을 잡고 편집한다. 해제시는 다시 동일 단축키를 누른다.
F7	Procedure에서 raise notice를 확인할때 필요
Ctrl + Space	테이블 조회시, 모든 컬럼 자동생성	select *   from table - * 입력 후 Ctrl + Space를 누르면 table에 있는 모든 컬럼 자동으로 생성된다. - alias를 a로 준 경우 a.* 입력 후 Ctrl + Space
Ctrl + Alt + Space	SQL 템플릿 사용
Ctrl + /	주석 및 해제	-- 를 통한 주석
Ctrl + Shift + /	주석 및 해제	/**/ 를 통한 주석
Ctrl + Shift + Y	소문자로 변환
Ctrl + Shift + X	대문자로 변환
Ctrl + D	현재 라인 삭제
Ctrl + Delete	포커스 기준으로 다음 단어 삭제
Ctrl + Backspace	포커스 기준으로 이전 단어 삭제
Ctrl + Shift + Delete	포커스 다음 단어부터 현재 라인 끝까지 삭제
Ctrl + Shift + Up(↑)/Down(↓)	현재 라인 상/하로 이동
Ctrl + Shift + Enter	포커스는 고정이 되면서 라인이 추가
Ctrl + Alt + J	포커스 기준으로 아래 라인 글 붙이기	Del로 붙였을때는 포커스 뒤로 글이 붙고, Ctrl + Alt + J로 붙였을때는 붙인 글 뒤로 포커스가 붙는다.
Ctrl + Alt + Up(↑)/Down(↓)	현재 라인 복사
Alt + Up(↑)/Down(↓)	현재 라인 기준으로 개행 아래 있는 블록 선택
Ctrl + Right(→)/Left(←)	다음/이전 단어로 이동
Ctrl + Shift + Right(→)/Left(←)	다음/이전 단어로 이동하면서 선택

오라클 데이터베이스에서 테이블스페이스를 생성할 때, 두 가지 유형의 테이블스페이스를 선택할 수 있다.
이 두 가지 유형 차이를 알아보도록 하자.

■ Bigfile 특징

1. 매우 큰 데이터 파일을 지원하는 특별한 유형의 테이블스페이스이다.

2. 하나의 데이터 파일만을 가지며, 대용량 데이터 관리 및 처리에 특화되어 있다.

■ Bigfile 장점

1. 하나의 데이터 파일만을 가지기 때문에, 파일 수 관리가 간단해지며 파일 할당과 관련된 오버헤드가 줄어든다.

2. 대규모 데이터베이스 시스템에서 높은 처리량과 입출력(IO) 성능을 제공한다.

3. 대량의 데이터를 빠르게 처리할 수 있는 높은 처리량을 제공한다.

4. 하나의 데이터 파일만을 가지기 때문에, 백업과 복구 작업을 간소화한다.

5. 많은 수의 작은 파일을 생성하지 않아도 되므로 데이터베이스의 설치 및 유지보수가 간소화된다.

6. 디스크 공간을 효율적으로 사용할 수 있다. 작은 파일들로 인해 발생하는 낭비를 줄일 수 있다.

■ Bigfile 단점

1. 작은 테이블이나 작은 규모의 데이터를 저장하기에는 적합하지 않을 수 있다.

    작은 크기의 테이블을 위한 것이 아니기 때문에 작은 규모의 데이터베이스에서는 오버헤드가 발생할 수 있다.

2. 파일에 손상이 발생하면 해당 파일 전체를 복구해야 한다. 이로 인해 복구 시간이 증가할 수 있다.

3. 하나의 큰 데이터 파일로 구성되기 때문에 데이터 파일의 크기가 계속 증가할 수 있다. 따라서 데이터베이스 용량 관리가 중요한데, 이를 관리하는데 어려움이 있을 수 있다.

4. 작은 크기의 테이블을 처리할 때에는 성능상의 제약이 있을 수 있다.

■ Smallfile 특징

1. 작은 크기의 데이터 파일을 사용하는 특별한 유형의 테이블스페이스이다.

2. 주로 작은 규모의 데이터베이스나 작은 테이블을 다룰 때 유용하다.

■ Smallfile 장점

1. 작은 규모의 데이터를 효율적으로 관리할 수 있다. 작은 테이블을 저장하거나 작은 규모의 데이터베이스를 다루는 데에 유리하다.

2. 데이터베이스의 용량 관리가 더욱 유연하다. 필요에 따라 데이터 파일을 추가하거나 관리하기 쉽다.

3. 백업 및 복구 작업이 더 빨리 수행될 수 있다.

4. 테이블이나 인덱스를 저장할 때 더 효율적인 성능을 제공한다.

5. 테이블스페이스의 구조가 단순하다. 데이터베이스의 구조를 이해하고 관리하기가 쉬워진다.

6. 시스템 리소스 사용량이 감소한다. 시스템 자원을 더욱 효율적으로 사용할 수 있도록 도와준다.

7. 대규모 데이터베이스에서 발생하는 관리 부담을 줄일 수 있다.

8. 작은 테이블과 데이터를 처리하는 데 최적화되어 있으므로 작은 크기의 데이터에 대한 액세스 성능을 향상시킬 수 있다.

■ Smallfile 단점

1. 대용량 데이터 처리에는 적합하지 않다. 대규모 데이터베이스에서는 데이터 파일의 수가 많아져 관리 오버헤드가 증가하며, 성능 이슈가 발생할 수 있다.

2. 데이터 파일의 수가 많아지면 백업 및 복구 시간이 늘어날 수 있다.

3. 작은 크기의 데이터 파일을 사용하기 때문에 데이터 파일의 크기가 계속해서 증가할 수 있다. 따라서 데이터베이스의 용량 관리가 어려워질 수 있다.

4. 작은 블록 크기를 사용하여 데이터를 저장하므로 대용량 데이터 처리나 대규모 트랜잭션 처리에는 성능 저하가 있을 수 있다.

5. 대량의 데이터를 검색하거나 조인하는 작업에서 성능 이슈가 발생할 수 있다.

오라클을 사용하면 SID, ServiceName이라는 말을 들어봤을 것이다.
항상 헷갈려왔던 SID, Service Name의 차이를 알아보고, SID를 여러 개 구성하는 이유를 살펴보자.

■ SID

1. 오라클 데이터베이스를 고유하게 식별하는 문자열로 데이터베이스 서버에는 여러 개의 인스턴스가 존재할 수 있다.

2. 각각의 오라클 인스턴스(Instance)는 유일한 SID를 가져야 한다.

3. 이 SID는 오라클 데이터베이스 서버가 실행될 때 자동으로 설정되는 값으로, 일반적으로 데이터베이스를 설치할 때 지정한다.

4. SID 추가 방법은 윈도우 - 시작 - Database Configuration Assistant을 클릭하여, 설치할 때와 동일한 방법으로 추가한다.

5. SID 확인방법

■ ServiceName

1. 오라클 데이터베이스에 접속할 때 사용되는 서비스 식별자이다.

2. 이는 클라이언트 응용 프로그램이 오라클 데이터베이스와 통신할 때 사용되는 문자열이다.

3. 오라클 데이터베이스를 사용하려는 클라이언트가 데이터베이스 서버에 접속하면, 서비스 이름을 지정하여 특정 데이터베이스 인스턴스에 연결한다.

4. 오라클 데이터베이스는 한 번에 여러 개의 서비스를 호스팅 할 수 있으며, 각 서비스는 고유한 서비스 이름을 가질 수 있다.

5. Service Name은 TNSNAMES.ORA 파일로 관리된다.

6. TNSNAMES.ORA 파일의 기본 위치는 클라이언트 시스템의 ORACLE_HOME 디렉터리에 있다. 일반적으로 ORACLE_HOME/network/admin 경로에 위치한다.

    (경로예제: Z:\...\product\12.2.0\dbhome_1\network\admin\)

7. tnsnames.ora파일은 아래와 같이 구성된다.

8. ServiceName 확인방법

■ SID 여러 개 구성하는 이유

1. 각각 다른 메모리 구성, 파라미터 설정, 데이터베이스 객체 등을 가질 수 있다. 이로 인해 데이터베이스의 특정 요구 사항에 맞게 최적화된 설정이 가능하다.

2. 데이터베이스 간에 서로 접근 권한을 분리할 수 있고, 또한 중요한 데이터를 보다 강력하게 보호할 수 있다.

3. 여러 개의 데이터베이스 인스턴스는 부하를 분산하여 데이터베이스 전체의 성능을 향상시킬 수 있다. 특정 데이터베이스에 발생하는 부하가 다른

    데이터베이스에 영향을 미치지 않도록 구성할 수 있다.

4. 여러 개의 데이터베이스 인스턴스를 이용하여 고가용성 및 장애 복구를 구현할 수 있다. 하나의 데이터베이스가 장애를 겪더라도 다른 데이터베이

    스가 가용하게 유지될 수 있다.

■ INS-35075 오류 - 오라클 완전 삭제 방법

1. 윈도우 - 시작 - 서비스를 입력한다.

2. 서비스를 실행하여, Oracle에 관련된 서비스를 중지시킨다.

3. 오라클이 설치되어 있는 폴더(.... product\12.2.0\dbhome_1\ 밑에 있음)로 가서 deinstall.bat을 실행시킨다.

4. 삭제가 완료되었으면, 실행창(Ctrl+R)을 실행시킨다.

5. 실행창에 regedit를 입력한다.

6. 레지스트리에서 아래 목록을 삭제한다.

  - HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE 있으면 삭제

  - HKEY_LOCAL_MACHINE\SYSEM\ControlSet001\Service에서 Oracle로 시작하는 것 있으면 삭제

  - HKEY_LOCAL_MACHINE\SYSEM\ControlSet002\Service에서 Oracle로 시작하는 것 있으면 삭제

  - HKEY_LOCAL_MACHINE\SYSEM\CurrentControlSet\Service에서 Oracle로 시작하는 것 있으면 삭제

  - HKEY_CLASSES_ROOT ORACLE, Ora로 시작하는 거 있으면 삭제

7. 6번까지 완료되었으면, 반드시 컴퓨터를 재부팅하고 다시 설치한다.

■ 오라클이 완전히 삭제되지 않는 이유

1. 대규모 데이터베이스 관리 시스템으로, 매우 복잡한 설치 구조를 가지고 있다. 이로 인해 제거 과정도 매우 복잡하고 시스템의 여러 부분에 설치되어 있을 수 있다.

    설치 프로세스 중에 생성된 여러 파일과 레지스트리 항목들을 하나하나 식별하여 삭제해야 하므로 완전한 제거가 어려울 수 있다.

2. 다른 소프트웨어와 강력한 의존성을 가질 수 있다.

    예를 들어, 다른 애플리케이션 또는 시스템 프로세스가 오라클 데이터베이스와 연동되어 작동하거나, 다른 프로그램이 오라클 클라이언트를 사용하는 경우 등이 있다.

3. 설치 과정에서 서비스와 백그라운드 프로세스를 생성하여 데이터베이스 관리를 수행한다.

    이러한 서비스와 프로세스는 시스템이 부팅될 때 자동으로 실행되며, 설치된 오라클을 완전히 제거하기 위해서는 이러한 서비스와 프로세스도 중단해야 한다.

4. 대량의 데이터 파일과 로그 파일을 생성한다.

    이러한 파일들은 데이터베이스 운영에 필수적이지만, 설치된 오라클을 삭제하더라도 데이터 파일과 로그 파일은 그대로 남을 수 있다.

    이로 인해 디스크 공간을 차지하게 되고, 완전한 삭제를 어렵게 만들 수 있다.

■ 에러 과정 및 해결

1. impdp를 통하여 database import 중 에러가 발생

2. Z:\998.Oracle\oradata\orcl\ (경로는 사용자마다 다를 수 있음) 안에 만들어 둔 .DBF을 삭제

3. DBeaver를 통하여 오라클을 접속하려고 하는데 ORA-01033 에러 발생

4. 아래 서비스를 재가동해 봄. - 여전히 되지 않음

5. 명령 프롬프트(cmd) - sqlplus 접속 - shutdown 및 시작 명령어 실행 (빨간색 글씨) - 여전히 안됨

6. 아래와 같이 다시 실행 후, DBeaver 접속 시도 - 성공

7. 아래와 같은 오류 메시지가 나오면 데이터 파일명이 아직 drop이 안된 것이 있는 것으로, 메시지가 안 나올 때까지 6번과 같이 반복 실행한다.

■ 오라클 12C 다운로드

1. 오라클 다운로드 사이트 : https://edelivery.oracle.com/osdc/faces/Home.jspx

2. 다운로드를 위해서는 계정이 있어야 한다. 계정이 있으면 Sign In, 없으면 계정을 생성한다.

3. 필자는 계정이 있으므로 사용자 이름 및 암호를 입력하고 로그인

4. 오라클 설치버전을 선택한다. 설치버전을 클릭하고, 우측 상단의 Continue버튼을 클릭한다.

5. 설치할 패키지를 체크한다. 필자는 서버와 클라이언트만 필요하기 때문에 위의 2개를 체크하고 Platforms/Languages를 선택한다.

    (필자는 OS가 Microsoft Windows x64 (64-bit)를 선택), 이후 우측 상단 또는 하단의 Continue 버튼을 클릭한다.

6. 하단의 체크란에 체크하고 Continue 버튼을 클릭한다.

7. Download 할 파일을 확인하고 Download 버튼을 클릭한다.

8. 다운로드가 완료되면 아래와 같은 파일이 다운로드 폴더 안에 생성된다.

9. 파일을 클릭하면 아래 이미지와 같이 실제 파일을 다운로드할 수 있는 창이 뜬다. 다운로드 폴더를 선택하고 Next를 눌러 파일을 다운로드한다.

10. 압축파일 V839963-01.zip을 풀고 setup을 클릭하여 설치를 시작한다. [My Oracle Support를 통해 보안 갱신 수신 체크]를 해제하고 다음을

      클릭한다. 그럼 아래와 같이 경고 메시지가 뜨는데, 예를 클릭한다.

11. 순서대로 아래 이미지와 같이 설치를 진행한다.

12. Oracle Base 등 경로에는 한글 및 특수문자가 들어가지 않도록 하며, 전역 데이터 베이스 비밀번호는 대소문자, 숫자 포함을 권장하나, 간단하게 입력해도 무방하다.

      설정한 비밀번호는 system 계정의 비밀번호로, 사용자를 만들지 않고 로그인할 때 필요하다.

      (설치 도중 에러 발생, 14번 참고 : Failed to add [Computer Name] install user to %2% group.)

13. 설치를 완료했으면, 오라클이 접속되는지 확인해 본다. (Oracle SQL Developer를 통해 확인)

      항목을 입력하고 테스트 버튼 클릭, 왼쪽 하단 상태가 성공이면 접속 완료된 것이다.

      (로그인 도중 에러 발생, 14번 참고 : ORA-01017: 사용자명/비밀번호가 부적합, 로그온 할 수 없습니다.)

    - 접속 이름 : 아무거나 입력

    - 사용자 이름 : system

    - 비밀번호 : 오라클을 설치하였을 때 지정한 비밀번호

    - 호스트 이름 : local에 접속할 것이므로, localhost

    - 포트 : 오라클에서 사용하는 기본포트로 1521

    - SID : 오라클을 설치하였을 때 만든 orcl

14. 위와 같은 절차로 진행하였을 때, 순조롭게 오라클이 설치될 수도 있지만, 그렇지 않은 경우 여러 에러에 부딪힐 수도 있다.

      설치하면서 났던 에러를 정리해 보았다. - 오라클 12C 에러 (설치, 접속)

오라클 12C 설치를 설치 및 접속하는 동안 여러 에러를 만날 수 있었는데,
에러 종류와 해결 방법을 알아보자.

■ 오라클 12C 설치 에러

1. 에러 메시지 : Failed to add [Computer Name] install user to %2% group.

  - Computer Name은 각 PC에 따라 명칭이 달라진다.

  - 이 에러는 사용자 이름과 컴퓨터 이름과 같으면 안 된다고 한다. 무슨 말인지 이해를 못 해서 무시하고 계속 설치를 진행한 결과 사용하는 데는 문제가 없었다.

■ 오라클 12C 접속 에러

1. DBeaver를 이용하여 접속을 시도했으나 다음과 같은 에러 메시지가 발생하였다. (초기 비번 대소문자 혼용해서 만듦)

  - ORA-01017: 사용자명/비밀번호가 부적합, 로그온 할 수 없습니다.

2. 명령 프롬프트(CMD)에 접속하여 다음과 같은 명령어를 입력한다.

3. SQL에 접속되었으면, 다음과 같은 명령어를 입력하여 대소문자 구분 설정값을 확인한다.

  -  설정값 : TRUE (대소문자를 구분한다는 뜻)

4. 대소문자 구분 안 하게 설정값을 변경한다.

5. 그래도 접속이 되지 않는다면, 비밀번호를 재설정한다. (필자는 1로 변경)

6. 그래도 접속이 되지 않는다면, 대소문자 설정값을 다시 구분하게 설정한다.

7. 이렇게 반복해서 접속이 완료되었다.

서버에 postgresql 설치했으면, 외부 클라이언트에서 서버에 접속할 수 있도록 설정을 해주어야 한다.
서버가 Ubuntu일 경우와 Windows일 경우 설정하는 방법,
설정 후 DBeaver에서 접속하는 방법을 알아보도록 하자.

■ 서버가 Ubuntu일 경우

1. postgres에 접속한다.

2. pg_hba.conf 파일 위치를 확인한다.

3. pg_hba.conf 파일 내용을 수정한다. (0.0.0.0/0으로 수정)

5. postgresql.conf 파일 내용을 수정한다.

  - listen_addresses = '*'로 수정하고, 앞에 # 표시도 제거(주석 해제)

6. 수정 후 postgres 계정에서 나온다.

7. postgresql 서비스를 재실행한다.

8. DBeaver를 실행하여 Host 부분을 서버 IP주소로 변경하고 Test Connection.. 클릭하면 Connected 메시지와 함께 팝업이 뜨면 접속 성공!

참고) postgres 비번을 모를 경우 설정방법

■ 서버가 Windows일 경우

1. C:\Program Files\PostgreSQL\15\data 폴더 안에 pg_hba 파일을 수정한다. (경로는 다를 수 있음)

2. C:\Program Files\PostgreSQL\15\data 폴더 안에 postgresql 파일을 수정한다. (경로는 다를 수 있음)

3. postgresql 서비스를 재실행한다.

4. DBeaver를 실행하여 앞서했던 방식과 동일하게 접속한다.

■ postgresql 다운로드

1. postgresql 다운로드 : https://www.postgresql.org/download/

2. 아래 Windows 클릭 후, Download the installer 클릭한다.

3. 원하는 버전을 클릭하여 다운로드한다. (필자는 15.3을 선택)

■ postgresql 설치

1. postgresql 다운로드 파일을 클릭하여 설치를 진행한다.

2. 아래 이미지 순서대로 설치한다.

3. postgreSQL Server : PostgreSQL 서버 설치

    pgAdmin 4 : postgresql 서버에 접속하기 위한 클라이언트 프로그램

    Stack Builder : postgresql의 추가적인 유틸리티 프로그램

    Command Line Tools : 말 그대로 Command Line Tools로 Command Line으로 postgresql로 접속할 수 있다.

4. password는 초기에 postgresql 접속할 때 사용한다.

5. postgresql port는 기본적으로 5432를 사용하지만, 변경해도 무방하다.

6. Locale은 Korean, Korea로 변경한다. (데이터에 한글이 들어올 수 있으므로..)

    Locale 설정은 아래와 같은 영향을 준다.

  - 대소문자 구분

  - 문자열 정렬

  - like문에서 인덱스 사용여부 결정

7. 설치가 완료되면 작업 관리자를 실행하여 postgresql 서버가 실행되고 있는지 확인한다.

■ postgresql 실행

1. 클라이언트 프로그램을 사용하여 postgresql에 접속한다.

    postgresql을 처음 설치할 때, pgAdmin4 설치 옵션 체크를 하고 설치를 하였기 때문에 별도로 다운로드하지 않아도 된다.

    pgAdmin4를 실행하여 postgresql에 접속한다. (접속 비밀번호는 설치할 때 설정했던 비밀번호)

2. postgresql 클라이언트 프로그램은 pgAdmin4 말고도 DBeaver와 같은 많은 프로그램이 있기 때문에, 다른 클라이언트 프로그램으로 접속하려

    면 추가적인 설치를 통하여 사용하면 된다. (DBeaver 추천)

참고) postgresql 외부 접속 방법 : https://200-rush.tistory.com/entry/PostgresqlOutConnect

■ postgresql 삭제방법

1. postgresql 서비스 상태

2. postgresql 서비스 중지

3. postgresql 서비스 비활성화

4. postgresql 삭제

5. postgresql 설치된 패키지 확인

■ postgresql 설치방법

1. postgresql 설치가능한 버전 확인

2. 패키지 최신화

3. 버전을 확인했으면 postgresql 설치 (2개 모두 postgresql 설치 명령어로 하나만 선택하여 실행한다.)

(참고)

- postgresql-contrib 설치여부 (추가 유틸리니 및 기능추가 설치여부)

- 어떤 것을 설치해도 무방

- sudo apt install -y postgresql    # -y를 써주면 설치하면서 나오는 yes/no를 무조건 yes로 설치한다.

- 다음과 같은 메시지와 같이 설치 안될 때 재부팅

  E: Could not get lock /var/cache/apt/archives/lock. It is held by process 3072 (unattended-upgr)

  N: Be aware that removing the lock file is not a solution and may break your system.

  E: Unable to lock directory /var/cache/apt/archives/

4. postgresql 상태확인 (2개 모두 상태확인 명령어)

5. postgresql 프로세스 확인

6. postgresql 리스너 확인

7. postgresql 시작

8. postgresql 접속

9. 사용자 확인 등 명령어

10. postgres 나오기

참고)

postgresql 삭제방법 : https://200-rush.tistory.com/entry/UbuntuPostgresqlDelete

postgresql 외부 접속 방법 : https://200-rush.tistory.com/entry/PostgresqlOutConnect