SQL Developer 다운로드 및 사용법
스프링/스프링 부트 웹 프로젝트 준비하기 (5) DBMS 클라이언트
데이터베이스(database, DB)를 사용하려면 데이터베이스 관리 시스템(Database Management System, DBMS)이 필요하고, DBMS를 사용하려면 DBMS 클라이언트 프로그램(DBMS client program)이 필요합니다. DBMS 클라이언트 프로그램이란 DB 서버에 접속하고 DB를 사용할 수 있도록 DBMS 기능을 사용자 인터페이스(user interface, UI)로 제공하는 프로그램입니다. 이번 시간에는 오라클 DBMS 클라이언트 프로그램 중 윈도우(Windows) 그래픽 유저 인터페이스(graphic user interface, GUI)에서 사용할 수 있는 SQL Developer를 설치하고 사용하는 법을 알아보겠습니다. 오라클 DBMS 설치 방법은 DBMS 설치하기(오라클 21c XE, MySQL)를 참조해 주세요.
다운로드하기
오라클 공식 홈페이지 SQL Developer Downloads에 들어가면 운영체제별 SQL Developer 목록이 나타납니다. 여기서 사용하고 있는 운영체제의 종류와 JDK 유무에 따라 적합한 버전을 다운로드합니다. 글에서는 JDK가 없는 윈도우 버전을 다운로드하겠습니다. JDK를 설치하고 환경변수 값을 설정하는 방법은 JDK 다운로드 및 환경변수 설정 방법(JDK 1.8, JDK 11)을 참조해 주세요.
다운로드 버튼을 클릭하면 아래와 같이 오라클 라이선스 정책에 동의 여부를 묻는 창이 나옵니다. 사용을 위해 체크 버튼을 눌러줍니다.
다운로드 버튼을 클릭하면 오라클 로그인 화면이 나타납니다. 오라클에 회원가입이 되어 있지 않다면 회원 가입을 진행한 후 다운로드를 하시면 됩니다.
다운로드가 완료되면 다운로드한 zip 파일을 클릭한 후 원하는 경로에 압축을 풀어줍니다.
압축을 푼 후 해당 폴더에 들어가면 다음과 같이 sqldeveloper.exe 파일을 확인할 수 있습니다. 파일을 클릭해서 실행해 줍니다.
SQL Developer를 처음 실행하면 다음과 같이 JDK 경로를 설정하는 안내창이 나타납니다. 여기에 설치한 JDK 폴더의 경로를 입력해 줍니다.
'OK' 버튼을 누르면 오라클 SQL Developer가 실행됩니다.
사용법
DB 접속 정보 생성하기
오라클 데이터베이스는 컨테이너 데이터베이스(container database, CDB)와 플러그인 가능 데이터베이스(pluggable database, PDB)로 나뉩니다. CDB는 전체 DB입니다. PDB는 CDB 내부에 끼웠다 뺏다 할 수 있는(pluggable) 독립적인 미니 DB입니다. CDB가 아파트 단지라면, PDB는 이 아파트 단지를 구성 하는 각 아파트 동입니다. 각 아파트 동이 별도의 세대로 구분되는 것처럼, 각 PDB도 자체 스키마와 객체를 가지며, 독립적인 DB처럼 동작합니다. 세입자(tenants)를 여럿 둘 수 있다는 의미에서 CDB는 다중 테넌트 컨테이너 데이터베이스로 작동합니다.
CDB는 루트 컨테이너 CDB$ROOT와 시드 PDB PDB$SEED를 가지고 있습니다. 루트 컨테이너는 아파트 단지의 관리사무소처럼 오라클의 메타데이터와 공통 사용자를 관리합니다. 시드 PDB는 새로운 PDB를 생성하는 데 사용하는 템플릿(template)입니다. 시드 PDB는 아파트 단지의 모델하우스처럼 아파트 단지(CDB)에 새로 입주하는 세대(PDB)들이 복사할 때 참고하는 틀입니다.
CDB를 직접 사용하면 위험부담이 큽니다. 전체 데이터베이스에 손상을 가할 수 있기 때문입니다. 반면, PDB는 가상의 미니 DB여서 메인 DB를 다루는 것에 비해 부담이 덜합니다. 따라서 CDB에 PDB를 만들어서 사용하는 것이 좋습니다. 먼저, CDB의 관리자 계정으로 DB 서버에 접속하는 법을 알아보겠습니다.
CDB 관리자 계정으로 DB 서버에 접속하기
메인 화면의 [Connections] 탭에서 연두색으로 된 [add +] 아이콘을 선택합니다.
그러면 신규 접속 정보를 생성하는 창이 나타납니다. 각 정보가 의미하는 바는 다음과 같습니다.
- 연결 이름: 원하는 연결 이름
- 사용자 이름: DB의 사용자 이름
- 비밀번호: DB의 비밀번호
- 호스트네임(hostname): DB 서버의 IP 주소 또는 도메인 이름
- 포트(port): DB 서버가 클라이언트의 연결을 수신하는 네트워크 포트
- SID(SystemID): DB 인스턴스를 식별하는 고유한 이름, 특정 DB 인스턴스 1개에 연결하는 데 사용
- 서비스 네임(service name): DB 서비스를 식별하는 논리적인 이름, 여러 개의 인스턴스가 있는 경우 클라이언트가 특정 인스턴스에 연결하는 대신 서비스를 통해 연결할 수 있도록 함
여기에 아래와 같이 접속 이름(name)과 사용자 이름 및 비밀번호를 입력합니다.
사용자 이름은 관리자 계정인 sys 또는 system을, 비밀번호란에는 오라클 DBMS 설치 시 지정한 관리자 계정 비밀번호를 입력합니다. sys 계정은 DB에 대한 모든 권한을 지닌 최상위 관리자 계정입니다. system 계정은 DB 관리를 할 수 있는 일반 관리자 계정입니다. sys 계정으로는 DB 생성과 삭제를 할 수 있지만, system 계정은 이 작업을 할 수 없습니다. 오라클에는 sys와 system 계정은 오라클 DBMS 설치 시 자동으로 생성됩니다. 여기서는 계정이 아니라 데이터베이스 접속 정보를 생성하는 것입니다.
sys 계정을 선택했다면, 롤(role)은 기본값에서 'SYSDBA'로 변경해 줍니다. sys as sysdba 명령은 sys 사용자로 데이터베이스에 연결하되, sysdba 권한으로 연결하겠다는 의미입니다. sysdba는 오라클 데이터베이스에서 가장 강력한 사용자로 연결해야 하는 경우에 사용됩니다.
다음은 실제로 데이터베이스에 접속이 되는지를 확인할 차례입니다. 테스트 버튼을 클릭합니다. 왼편의 주황색으로 표시된 부분에서처럼 상태가 '성공'으로 나타나면 '접속' 버튼을 눌러줍니다. 새로 만든 정보가 성공적으로 만들어지면 다음과 같은 화면이 나타납니다.
여기서 만들어진 서버 접속 정보를 클릭한 후 아래와 같이 오른쪽 버튼을 눌러 '접속'을 눌러주세요.
그러면 비밀번호를 입력하는 창이 나타납니다. 비밀번호로는 위에서 접속 정보 생성 시 입력한 비밀번호를 입력해 줍니다.
만일 DB 접속 시 비밀번호를 매번 입력하고 싶지 않다면, '비밀번호 저장' 부분에 체크하면 됩니다. 사용자 정보 인증이 완료되면 다음과 같이 오라클 DBMS의 DB 서버에 접속할 수 있게 됩니다.
만일, sys 계정의 비밀번호를 변경하려면 다음과 같은 SQL 문을 입력하면 됩니다.
sqlALTER USER SYS IDENTIFIED BY 새로운_비밀번호;
지금까지 CDB의 관리자 계정으로 DB 서버에 접속하는 방법을 알아보았습니다. 이번에는 PDB의 관리자 계정으로 PDB 서버에 접속하는 법을 살펴보겠습니다.
PDB 관리자 계정으로 DB 서버에 접속하기
PDB의 관리자 계정으로 DB 서버에 접속하는 방법도 CDB의 관리자 계정으로 접속하는 방법과 동일합니다. 다만, CDB에서와 달리 PDB에서는 [세부정보]를 SID에서 서비스 이름으로 선택해 주고, 서비스 이름을 지정해 줍니다. 여기서는 xe라는 CDB의 첫 번째 PDB라는 뜻에서 xepdb1를 사용합니다. xepdb1은 오라클 12c 이상 버전에서 자동으로 생성됩니다. 다음 SQL 쿼리문을 이용하면 오라클 DB에서 현재 인스턴스와 연관된 PDB의 이름이 반환됩니다.
sqlSELECT NAME FROM v$pdbs;
여기서 생성하는 접속 정보는 PDB의 관리자 계정 sys입니다. 위에서 만든 CDB의 관리자 계정 sys와 다릅니다.
테스트 후에는 접속 버튼을 눌러 PDB sys 계정의 접속 정보를 생성합니다. 접속 정보가 생성되면 다음과 같이 표시됩니다.
PDB를 SQL 쿼리문으로 생성하려면 다음 명령어를 사용하면 됩니다.
sqlCREATE PLUGGABLE DATABASE pdb_nameADMIN USER admin_user IDENTIFIED BY passwordROLES = (DEFAULT)DEFAULT TABLESPACE usersDATAFILE '/path/to/datafile.dbf' SIZE 250M AUTOEXTEND ON;
PDB에 사용자 접속 정보 생성하기
이번에는 PDB에 사용자 접속 정보를 생성해 보겠습니다. 아래와 같이 접속 이름 및 사용자 이름과 비밀번호를 입력합니다. 롤은 기본값으로 지정합니다.
접속 버튼을 누르면 아래와 같이 PDB에 사용자 접속 정보가 생성됩니다.
이 접속 정보는 DBA(database administrator)에서 만든 사용자 계정으로 접속할 때 사용할 수 있습니다.
DBA 콘솔 사용하기
DBA 콘솔이란 데이터베이스 관리자가 DB를 관리하는 데 사용하는 도구입니다. DBA 콘솔에서는 DB의 성능 모니터링, 백업 및 복구, 사용자 및 권한 관리, 스토리지 관리 등 다양한 데이터베이스 관리 작업을 수행할 수 있습니다. DBA 콘솔을 사용하려면 DBA 계정으로 접속해야 합니다. 먼저 DBA 계정으로 접속하는 방법을 알아보겠습니다.
DBA 계정으로 접속하기
이전에 생성한 접속 인스턴스를 사용하여 DBA 계정으로 접속하려면 다음 절차를 따르면 됩니다.
- [보기] 메뉴에서 'DBA' 선택하기
- 접속 인스턴스 선택하기
테이블스페이스 생성하기
테이블스페이스(Tablespace)란 테이블(table) 형태로 된 개념상의 데이터를 저장하는 오라클 DB 내의 논리적 공간입니다. 오라클 DB에는 다음과 같은 두 가지 유형의 테이블스페이스가 있습니다.
- 기본 테이블스페이스: DB 오브젝트 내 실제 데이터를 저장하는 공간으로 테이블, 인덱스, 클러스터 등의 DB 오브젝트 저장
- 임시 테이블스페이스: DB 작업 중 발생하는 임시 데이터를 저장하는 공간
기본 테이블스페이스와 임시 테이블스페이스의 차이점은 저장되는 데이터의 유형과 지속성입니다. 일반 테이블 스페이스에는 실제 데이터가 저장되며, 이 데이터는 영구적으로 유지됩니다. 반면, 임시 테이블스페이스에는 임시 데이터가 저장되며, 이 데이터는 세션이 종료되거나 트랜잭션이 커밋되면 자동으로 삭제됩니다. 테이블스페이스는 다음 경로에서 만들 수 있습니다.
- [DBA] - [접속 인스턴스] - [저장 영역] - [테이블스페이스] - [새로 만들기]
'새로 만들기' 버튼을 클릭한 후 다음과 같이 새로 생성할 테이블스페이스의 정보를 입력합니다. 입력할 주요 정보는 다음과 같습니다.
- 이름: 생성할 테이블스페이스의 이름
- 테이블스페이스 유형: 영구 또는 임시
- 파일 사양: 테이블스페이스에 저장할 데이터 파일 이름과 파일을 저장할 경로, 파일 크기
- 크기: 테이블스페이스의 초기 크기(테이블스페이스가 사용할 수 있는 디스크 공간의 양)
- 자동 확장 설정: 테이블스페이스가 필요에 따라 자동으로 확장
- 다음 크기: 자동 확장이 활성화된 경우, 다음으로 확장할 테이블스페이스 크기
- 최대 크기: 자동 확장이 활성화된 경우, 테이블스페이스가 확장될 수 있는 최대 크기
'확인' 버튼을 누르면 다음과 같이 새로운 테이블스페이스가 생성됩니다.
테이블스페이스는 다음 SQL 문으로 생성할 수도 있습니다.
sqlCREATE TABLESPACE 테이블스페이스_이름DATAFILE '데이터파일_경로'SIZE 크기;
위와 같은 방법으로 임시 데이터를 저장하는 테이블스페이스도 만들어 보겠습니다. 임시 테이블스페이스를 만들 때는 테이블스페이스 유형을 '임시'로 선택하면 됩니다.
'확인' 버튼을 누르면 아래와 같이 새로운 임시 테이블스페이스가 만들어집니다.
테이블스페이스를 사용하려면 사용자 계정을 생성해야 합니다.
사용자 계정 생성하기
DBA 콘솔에서 사용자 계정은 다음 경로에서 생성할 수 있습니다.
GUI
- [DBA] - [보안] - [사용자] - [오른쪽 버튼 클릭] - [새로 만들기]
'새로 만들기' 버튼을 클릭한 후 아래와 같이 사용자 이름과 비밀번호 및 테이블스페이스를 지정해 줍니다.
SQL 명령
SQL 명령을 이용해 사용자 계정을 생성하려면 SQL 워크시트에 다음 SQL 명령을 이용할 수 있습니다.
sqlCREATE USER username IDENTIFIED BY password;
권한 부여하기
계정을 생성한 다음에는 해당 계정에 권한을 부여해야 합니다. 권한을 부여해야 SQL 작업을 할 수 있습니다. 권한은 GUI에서 부여하는 방법과 SQL 명령어로 부여하는 방법이 있습니다.
GUI
'시스템 권한' 탭으로 이동한 후 '모두 관리자'를 클릭해서 모든 권한을 부여합니다. 하지만, 일반적인 데이터베이스 관리 작업을 수행하는 경우, 일부 권한은 제외하는 것이 좋습니다.
예를 들어, 아래 권한은 특수한 작업을 수행하는 데 필요한 권한입니다.
- SYSKM: Transparent Data Encryption (TDE) 및 Oracle Key Vault와 같은 암호화 관련 작업을 수행하는 데 필요한 권한을 가진 역할
- SYSOPER: DB 시작 및 종료, 인스턴스 관리, 아카이브 로그 관리 등의 작업을 수행하는 데 필요한 권한을 가진 역할
- SYSRAC: Oracle Real Application Clusters (RAC) 환경에서 클러스터 관리 작업을 수행하는 데 필요한 권한을 가진 역할
이러한 권한은 데이터베이스의 보안과 안정성을 위해 제한적으로 사용되어야 합니다. 따라서, 일반적인 데이터베이스 관리 작업을 수행하는 경우 제외하는 것이 좋습니다.
적용 버튼을 누르면 다음과 같이 TEST라는 이름의 새로운 사용자가 생성됩니다.
이렇게 DBA 메뉴에서 사용자 계정을 생성한 후, 위에서 생성한 PDB 사용자 접속 정보인 TEST로 접속하면 해당 사용자 계정으로 DB 서버에 접속할 수 있습니다. 사용자 계정을 만들려면 워크시트에서 다음 SQL 쿼리문을 실행해도 됩니다.
sqlCREATE USER username IDENTIFIED BY mypasswordDEFAULT TABLESPACE TEST_TABLESPACETEMPORARY TABLESPACE TEST_LOGSPACE;
위 쿼리문을 실행하면 User USERNAME이 (가) 생성되었습니다.라는 결과가 출력됩니다.
SQL 명령
계정에 권한을 부여하려면 SQL 명령어를 사용해도 됩니다. 예를 들어, 사용자 계정에 접속(CONNECT) 권한과 DBA 권한을 부여하고 싶다면 다음 명령어를 사용할 수 있습니다.
sqlGRANT CONNECT, DBA TO TEST; -- 사용자에게 접속(connect) 권한과 DBA 권한을 부여
이 명령은 TEST 사용자가 데이터베이스에 접속하고 DBA 역할을 수행할 수 있도록 합니다.
원격으로 접속하기
기본적으로 Oracle XML DB HTTP 서버는 로컬 액세스만 허용합니다. 즉, DB 서버에서만 HTTP 요청을 수락합니다. 하지만, DB를 이용하는 사용자의 기기와 DB가 설치된 기기가 다를 수도 있습니다. 이 경우, 원격으로 DB에 접속해야 합니다. 그러려면 오라클의 DB 서버 외부에서도 HTTP 요청을 수락하도록 설정하면 됩니다. 다음 SQL 쿼리문을 실행하면 Oracle XML DB HTTP 서버의 원격 액세스가 활성화됩니다.
sqlEXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
원격 엑세스를 활성화한 후에는 접속 정보 생성 시 입력한 호스트 이름을 localhost에서 DB 이용자의 기기와 DB가 설치된 기기가 공유하는 IP주소로 변경하면 됩니다. 예를 들어 공유기의 IPv4 주소가 192.168.0.0이라면 localhost 대신 이 IPv4 주소를 입력하면 됩니다. 현재 IP 주소는 cmd에서 ipconfig를 입력하면 확인할 수 있습니다.
포트 확인/변경하기
오라클을 이용해 웹 개발 시에는 아파치 톰캣(Apache Tomcat)과 같은 WAS와 충돌이 발생할 수 있습니다. 오라클과 톰캣 모두 8080 포트를 사용하기 때문입니다. 오라클과 톰캣을 함께 사용할 때는 둘 중 한 프로그램의 포트를 1024번 이상의 포트 번호로 변경해 주어야 합니다. 0번 ~ 1023번의 포트 번호는 대부분 시스템 사용을 위해 예약되어 있습니다. 톰캣 서버의 포트 번호를 변경하는 방법은 아파치 톰캣(Apache Tomcat) 다운로드 및 환경설정하기을 참조해 주세요.
오라클의 현재 포트 번호를 확인하려면 다음 SQL 쿼리문을 사용합니다.
sqlSELECT dbms_xdb.gethttpport() FROM dual; -- 현재 포트 번호 확인하기
오라클의 현재 기본 포트를 다른 포트로 변경하려면 아래 SQL 쿼리문을 사용하면 됩니다.
sqlEXEC dbms_xdb.sethttpport(8081); -- 포트 번호를 8081로 변경
지금까지 SQL Developer를 다운로드하고 사용하는 법을 살펴보았습니다. 오라클의 SQL Developer 외에도 DBeaver, HeidiSQL 등 다양한 DBMS 클라이언트 프로그램이 있습니다. 더 다양한 DBMS 클라이언트 프로그램의 목록은 Top 8 Free, Open Source SQL Clients to Make Database Management Easier 2023을 참조해 주세요.
참조 자료
- [1] 뉴렉처. (2019, 08 27). 오라클 데이터베이스 SQL 강의 4강 - 오라클 PDB 서버에 접속하기.[비디오 파일]. 검색경로 https://www.youtube.com/watch?v=jzPGKAU4fCU&list=PLq8wAnVUcTFVq7RD1kuUwkdWabxvDGzfu&index=4
- [2] 뉴렉처. (2019, 08 27). 오라클 데이터베이스 SQL 강의 5강 - 수업용 사용자와 데이터베이스 생성하기.[비디오 파일]. 검색경로 https://www.youtube.com/watch?v=vKd6V0cXoL4&list=PLq8wAnVUcTFVq7RD1kuUwkdWabxvDGzfu&index=5