[SQL BOOSTER] 1. 준비하기 - Mac OS에 오라클 DB 설치(Docker), DataGrip 설치

📚 SQL BOOSTER (유일환, DBian)

요즘 업무적으로 직접 쿼리를 짜거나 수정해야 할 일이 많아졌다.

그래서 동기들과 SQL 스터디를 시작하게 되었다.

이번 스터디 교재로 선정된 <SQL BOOSTER>라는 책 제목은 이 교재를 읽은 개발자들이 SQL 실력에 더 강력한 부스터를 장착하기를 바라는 의미에서 지어졌다고 한다.

 

내 SQL 실력에 강력한 부스터🚀를 장착하기 위해 앞으로 공부를 열심히 해야겠다!

 

우선 책의 실습 예제들을 풀어보기 위해 환경 세팅을 해보겠다.

책 속의 예제들은 오라클 11g Express Edition 기준으로 작성되었다.

 

MacOS에 Oracle 11g Express Edition 설치하기

순서

1. Docker 설치

2. oracle 11g XE 설치

3. SQL Plus에서 사용자 설정하기

 

참고로 설치 시점의 MacOS는 Ventura 13.6.3 버전이다.

 

1. Docker 설치

아래 url에서 Docker Desktop을 설치한다.

Docker 계정이 없는 경우에는 미리 홈페이지에서 계정을 만든 후 설치할 것을 권장한다.

 

https://www.docker.com/products/docker-desktop/

Docker Desktop: The #1 Containerization Tool for Developers | Docker

 

 

Mac은 Apple Chip과 Intel Chip 두 가지 선택 옵션이 있으니 자신의 사양에 맞게 선택하자.

나는 이미 Docker가 설치되어 있어서 버전 업데이트만 진행해주었다.

설치 후 Docker를 실행하여 화면 우측 상단에 Docker 아이콘이 뜨고 Docker Desktop is running이 표시되면 된다.

 

 

2. Oracle 11g XE 설치

1. 다운로드 할 이미지 검색하기

docker search oracle-xe-11g

 

터미널에서 위의 명령어를 통해 다운로드할 이미지를 검색한다.

 

 

검색된 목록 중에서 jaspeen/oracle-xe-11g를 통해 이미지를 다운로드 해보겠다.

 

2. 이미지 다운로드하기

docker pull jaspeen/oracle-xe-11g

 

위의 명령어를 통해 이미지를 다운로드한다.

 

 

다운로드가 성공적으로 완료되면 위와 같이 Status가 표시된다.

 

3. 컨테이너 생성하기

아래 명령어를 통해 oracle-xe-11g 라는 이름의 컨테이너를 생성한다.

docker run --name oracle-xe-11g -d -p 8080:8080 -p 1521:1521 jaspeen/oracle-xe-11g

 

그리고 아래 명령어를 통해 컨테이너가 제대로 생성되었는지 확인한다.

docker ps

 

 

3. SQL Plus 에서 user 설정하기

1. SQL Plus 실행하기

docker exec -it oracle-xe-11g sqlplus

 

위의 명령어로 SQL Plus를 실행할 수 있는데, oracle-xe-11g 자리에 자신이 생성한 컨테이너 이름을 넣어주면 된다.

 

2. system 계정으로 접속하기

Enter user-name : system
Enter password: oracle

 

system 계정으로 접속 시 위와 같이 user-name과 password를 입력한다.

비밀번호 입력시 화면상에서 입력값이 보이지 않기 때문에 오타에 유의해야 한다.

접속이 성공적으로 완료되면 다음과 같이 sql 명령문 입력이 가능해진다.

 

system/oracle 로 접속해서 내가 생성한 유저에 권한을 부여하다가 막혀버렸다.

오류 내용은 대략 다음과 같다.

"ORA-00942: table or view does not exist"

구글링한 결과 sysdba로 접속해서 권한을 부여하니 해결됐다.

 

1. sysdba로 접속하기

1-1. docker exec -it 컨테이너이름 bin/bash

docker exec -it oracle-xe-11g bin/bash

 

oracle-xe-11g 자리에는 docker 컨테이너 이름을 넣어준다.

 

1-2. root@컨테이너ID: /# sqlplus /nolog

sqlplus /nolog

 

1-3. conn sys/oracle as sysdba

conn sys/oracle as sysdba

 

정상적으로 접속되면 Connected. 가 표시되며 SQL 명령어를 입력할 수 있게 된다.

 

 

CH1. 준비하기

1-1. 연습을 위한 환경 구성

1-1-1. 연습용 테이블 스페이스 만들기

연습용 테이블 스페이스를 만들기 위해 sys 계정으로 오라클에 접속해 아래 명령어를 실행한다.

 

1. 테이블 스페이스 만들기

CREATE TABLESPACE ORA_SQL_TEST_TS DATAFILE 'ORA_SQL_TEST.DBA' SIZE 10G AUTOEXTEND ON NEXT 10M;

 

위의 명령어로 테이블 스페이스를 생성한다.

ORA_SQL_TEST_TS라는 이름의 테이블 스페이스를, 'ORA_SQL_TEST.DBA' 라는 파일로 생성한다.

테이블 스페이스의 크기는 10기가이며, 자동 확장 기능을 사용했다.

 

 

2. 테이블 스페이스 정보 확인하기

SELECT FILE_NAME, TABLESPACE_NAME, BYTES, STATUS FROM DBA_DATA_FILES;

 

 

다음은 내가 실제 수행한 작업은 아니지만 앞으로를 위해 테이블 스페이스와 관련된 참고사항을 정리한 내용이다.

 

3. (참고) 테이블 스페이스 크기 변경하기

ALTER DATABASE DATAFILE 'ORA_SQL_TEST.DBA' RESIZE 500M;

 

테이블 스페이스 크기를 500메가로 변경하는 명령어다.

 

4. (참고) 테이블 스페이스 삭제하기

DROP TABLESPACE ORA_SQL_TEST_TS INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

 

DROP TABLESPACE 테이블스페이스명 : 테이블 스페이스를 삭제하는 명령어

INCLUDING CONTENTS AND DATAFILES : 테이블 스페이스의 모든 세그먼트를 삭제하며 물리적 파일까지 삭제한다.

CASCADE CONSTRAINTS; : 삭제된 테이블 스페이스 내의 테이블의 기본키와 유일키를 참조하는 다른 테이블 스페이스의 테이블로부터 참조무결성 제약 조건을 삭제한다.

 

1-1-2. 연습용 사용자 만들기

1. 사용자 생성하기

CREATE USER test_user IDENTIFIED BY test_user DEFAULT TABLESPACE ORA_SQL_TEST_TS;

 

데이터베이스 사용자를 생성했다.

사용자 아이디와 로그인 비밀번호 모두 동일하게 test_user로 설정했다.

(원래는 사용자 아이디를 user로 생성하려 했는데 해당 이름으로는 생성할 수 없어서 test_user로 설정했다.)

 

 

2. 사용자 생성 확인하기

SELECT * FROM ALL_USERS;

 

 

TEST_USER가 잘 생성된 것을 확인할 수 있다.

 

3. (참고) 사용자 삭제하기

DROP USER test_user CASCADE;

 

위의 명령어로 test_user 를 삭제할 수 있다.

 

4. 사용자 권한 부여하기

GRANT CONNECT, RESOURCE TO test_user;

 

위의 명령어로 test_user 에게 접속 및 리소스 권한을 부여한다.

이 책의 Part2에서 성능 관련 내용도 다룰 것이므로 아래 권한들도 추가해놓았다.

 

GRANT ALTER SYSTEM TO test_user;
GRANT SELECT ON V_$SQL TO test_user;
GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO test_user;
GRANT SELECT ON V_$SQL_PLAN TO test_user;
GRANT SELECT ON V_$SESSION TO test_user;
GRANT EXECUTE ON DBMS_STATS TO test_user;
GRANT SELECT ON DBA_SEGMENTS TO test_user;

 

5. (참고) 사용자 권한 해제하기

REVOKE CONNECT, RESOURCE FROM test_user;

 

6. (참고) 사용자 비밀번호 변경하기

ALTER USER test_user IDENTIFIED BY "1qaz2wsx";

 

위의 명령어로 test_user의 로그인 비밀번호를 1qaz2wsx로 변경할 수 있다.

 

1-2. 연습용 데이터베이스 구성

1-2-1. 연습용 테이블 생성하기

강의 실습용 테이블을 생성하는 스크립트를 실행해서 테이블을 생성했다.

스크립트는 아래 주소에서 다운로드 받을 수 있다.

 

https://blog.naver.com/ryu1hwan/221525912860

Script Chapter.1.01,02 - DB,사용자,테이블 생성

 

이번에 생성한 테이블에 대해 간략하게 정리하면 다음과 같다.

 

1. 기준코드구분(C_BAS_CD_DV), 기준코드(C_BAS_CD) : 시스템 전반적으로 사용하는 기준코드

2. 지역(마스터, M_RGN) : 지역(region)을 관리

3. 고객(마스터, M_CUS) : 고객(customer) 정보를 관리

4. 아이템(마스터, M_ITM) : 쇼핑몰에서 판매하는 아이템(item, 상품)을 관리

5. 아이템단가이력(마스터, M_ITM_PRC_HIS) : 아이템의 판매 단가를 이력 구조로 관리

6. 아이템평가(실적, T_ITM_EVL) : 고객이 아이템을 평가한 기록

7. 주문(T_ORD), 주문상세(실적, T_ORD_DET) : 고객의 주문정보

 

(참고) 기준코드

기준코드는 시스템에서 사용하는 코드성 데이터다.

예를 들어, 지불유형(PAY_TP), 주문상태(ORD_ST), 아이템유형(ITM_TP)과 같은 코드성 데이터를 뜻한다.

이러한 기준코드는 시스템에 따라 공통코드, 기초코드, 마스터코드, 그룹코드와 같은 다양한 용어로 불린다.

 

1-2-2. 연습용 데이터 생성하기

연습용 데이터도 연습용 테이블과 같이 스크립트를 실행해서 생성했다.

연습용 데이터를 생성하는 스크립트에서는 카테시안 조인(CARTESIAN JOIN)과 CONNECT BY ROWNUM을 곳곳에 사용한다.

이번에는 그냥 테스트 데이터를 사용할 때 이러한 기술을 활용할 수 있다는 것만 알고 넘어가면 된다.

 

Mac OS에 DataGrip 설치하기

1. DataGrip 설치 경로 접속하기

https://www.jetbrains.com/datagrip/download

Download DataGrip: Cross-Platform IDE for Databases & SQL

 

2. Mac OS용 설치파일 다운로드

 

3. 다운로드된 dmg 파일 실행하여 설치 진행하기

 

DataGrip.app 아이콘을 드래그해서 Applications 폴더로 이동시킨다.

 

4. 설치된 DataGrip을 실행하기

 

5. 프로젝트 생성

5-1. New Project 생성하기

 

5-2. 원하는 경로 혹은 프로젝트 이름을 지정한뒤 OK 클릭

 

6. 사용자 연결

6-1. Data Sources 열기

File > Data Sources 를 선택한다.

단축키 : Command + ;

 

6-2. Data Source 추가하기

+ 버튼을 누르고 Oracle을 검색하여 선택한다.

 

6-3. 사용자 계정 연결하기

User, Password에 자신이 생성한 사용자 이름과 비밀번호를 입력한다.

나는 아까 생성한 test_user로 연결할 것이다.

입력 후 Test Connection을 선택해 정상적으로 연결이 되는지 확인한다.

 

6-4. 드라이버 다운로드

드라이버 다운로드가 필요한 경우 아래와 같은 창이 뜬다.

Download 버튼을 클릭하면 내 DB 버전에 맞는 드라이버 파일을 알아서 다운로드해준다.

 

Test Conncection에 성공하면 아래와 같이 Succeeded가 표시된다.

OK를 눌러 연결을 완료한다.

 

7. 테이블 및 데이터 확인하기

TEST_USER에 앞서 생성한 테이블과 데이터가 제대로 들어왔는지 확인한다.