객체 종류 1 - 데이터를 저장하는 데이터 사전과 테이블처럼 사용하는 뷰에 대하여

점심을 먹고 3시가 다 되어서 오후 공부를 시작해본다. 그럼 오후 공부를 시작해보자!

 

학습 목표

1. 데이터베이스를 위해 데이터를 저장한 데이터 사전
2. 테이블처럼 사용하는 뷰
3. 시스템 계정으로 뷰 사용하는 방법 배우기

---

 

 

객체

---

 

 

1. 데이터베이스를 위해 데이터를 저장한 데이터 사전

데이터 사전이란?

오라클 데이터베이스 테이블은 사용자 테이블과 데이터 사전으로 나뉜다. 사용자 테이블은 데이터베이스를 통해 관리할 데이터를 저장하는 테이블을 뜻한다. 앞에서 여러 SQL문에서 활용한 EMP, EDPT, SALGRADE 테이블이 바로 사용자 테이블이다.
데이터 사전은 데이터베이스를 구성하고 운영하는데 필요한 모든 정보를 저장하는 특수한 테이블로 데이터베이스가 생성되는 시점에 자동으로 만들어진다.

 

데이터 사전에는 DB 메모리 · 성능 · 사용자 · 권한 · 객체 등 오라클 DB 운영에서 중요한 데이터가 보관되어 있다. 만약 이 데이터에 문제가 발생한다면 오라클 데이터베이스 사용이 불가능해질 수도 있다.
따라서 오라클 데이터베이스는 사용자가 데이터 사전 정보에 직접 접근하거나 작업하는 것을 허용하지 않는다. 대신 데이터 사전 뷰를 제공하여 SELECT문으로 정보를 열람할 수 있게 해두었다.

데이터 사전 뷰는 용도에 따라 이름 앞에 다음과 같은 접두어를 지정하여 분류한다.

 

 

SCOTT 계정에서 사용 가능한 데이터 사전 살펴보기

 

 

 

USER_ 접두어를 가진 데이터 사전

USER_ 접두어로 시작하는 이름의 데이터 사전에는 현재 오라클에 접속해 있는 사용자가 소유한 객체 정보가 보관되어 있다. 예를 들어 현재 오라클 데이터베이스에 접속해 있는 SCOTT 계정이 소유하는 테이블 정보는 USER_TABLES를 사용한다.

SCOTT 계정이 가지고 있는 객체 정보 살펴보기

SCOTT 계정이 가지고 있는 테이블 이름을 알고 싶을 때 유용하다.

 

 

 

ALL_ 접두어를 가진 데이터 사전

오라클 데이터베이스에 접속해 있는 사용자가 소유한 객체 및 다른 사용자가 소유한 객체 중 사용이 허락되어 있는 객체 정보를 가지고 있다. SCOTT 계정으로 접속하여 ALLTABLES를 조회하면 SCOTT 계정이 사용할 수 있는 테이블 정보를 모두 보여 준다. ALL 객체를 명시할 때는 복수의 단어를 사용한다.

SCOTT 계정이 사용할 수 있는 객체 정보 살펴보기

 

 

 

DBA_ 접두어를 가진 데이터 사전

데이터베이스 관리 권한을 가진 사용자만 조회할 수 있는 테이블로서 SCOTT 계정으로는 조회가 불가능하다.

SYSTEM 계정으로 DBA_ 접두어 사용하기

---

 

 

2. 테이블처럼 사용하는 뷰

뷰란?

하나 이상의 테이블을 조회하는 SELECT문을 저장한 객체를 뜻한다. SELECT문에 저장하기 때문에 물리적인 데이터를 따로 저장하지 않는다. 따라서 뷰를 SELECT문의 FROM 절에 사용하면 특정 테이블을 조회하는 것과 같은 효과를 얻을 수 있다.

 

 

 

 

 

뷰의 사용 목적

많은 분량의 SELECT문 여러 개의 결과값을 다시 조인하고 서브쿼리로 WHERE 조건식에도 사용한다면 전체 SELECT문은 훨씬 더 커질 것 이다. 이럴 때 SQL문에서 자주 활용하는 SELECT문을 뷰로 저장해 놓은 후 다른 SQL문에서 활용하면 간단하게 사용할 수 있다.

중요한 개인 정보의 경우 해당 사용자에게 특정 테이블의 전체 조회 권한을 부여하는 것은 데이터 보안에 위협이 될 수도 있다. 테이블의 일부 데이터 또는 조인이나 여러 함수 등으로 가공을 거친 데이터만 SELECT하는 뷰 열람 권한을 제공한다면, 불필요한 노출을 막을 수 있다.

 

 

 

 

뷰 만드는 방법

이 예제는 가상의 테이블을 조회하는 것이고 실제로 뷰를 사용하려면 HR이나 SCOTT 계정이 아닌 SYSTEM 계정으로 접속해야 한다.

---

 

 

1. SYSTEM 계정 접속하기

사실 이 부분은 15장 사용자 권한에서 다루는 부분이지만 뷰를 생성하기 위해서는 이 방법을 알고 있어야 한다.

선생님이 수업 하셨던 내용을 뒤적거리며 어디서 다루었는지 드디어 찾았다. 조금 전에 너무 졸려서 15분 정도 낮잠을 자고 나서 다시 시작해본다. 처음에는 계정 접속하는 방법을 잊어버려서 몇 번 접속을 못했다.
이제 SYSTEM 계정으로 접속할 수 있게 되었다.

 

 

 

2. SYSTEM 계정 만들기

지난번에 SCOTT 계정을 만들 때 SYSTEM 계정을 만든줄 알았는데, 이번에 보니 아니었다. 그냥 오라클에서 접속해서 만드는 건 시스템 계정이 아니었다. 여기서 COMMEND 창에서 직접 만드는 것이다.
처음에는 이걸 하지 않고 접속하니 아이디와 비밀번호가 틀렸다는 메시지만 떴다.

이 실패 메시지는 락이 걸려있어서 권한을 부여해야 한다는 뜻이다.

 

 

 

3. 권한 부여해주기

 

시스템 계정으로 DCLTEST 이름을 하나 만들었고 거기에 권한을 부여해주었다.

 

 

이제 TEST를 하자 성공으로 뜬다. 사용자 권한을 부여하는 부분은 원래 15장쯤 등장하니 그때 학습하기로 하고 이 부분만 급한대로 먼저 실행해보았다.

 

 

 

4. SCOTT 계정에도 권한 부여해주기

왠지 이렇게 하면 될 것 같았는데 내 예상이 맞았다. 책에 있는대로 해보니 몇번이나 해도 계정 변경도 권한 부여도 되지 않았다. 이렇게 시스템 계정을 만들고 하니 잘 되었다. 이제 책에 있는 예제도 실습해볼 수 있다~

---

 

 

 

예제 실습해보기

뷰 생성하기

 

 

생성한 뷰 확인하기 1 - 오라클

오른쪽 열이 좀 많지만 거기서부터는 모두 NULL이다.

 

 

생성한 뷰 확인하기 2 - COMMEND 창

 

commend 창으로도 확인할 수 있다.

 

이렇게 생성한 뷰는 SELECT문의 FROM절에서 테이블처럼 지정하여 사용할 수 있다.

 

 

 

생성한 뷰 조회하기

 

 

 

뷰 삭제하기

---

 

 

인라인 뷰를 사용한 TOP-N SQL문

 

CREATE문을 객체로 만들어지는 뷰 외에 SQL문에서 일회성으로 만들어서 사용하는 뷰를 인라인 뷰(Inline View)라고 한다. SELECT문에서 사용되는 서브쿼리, WITH절에서 미리 이름ㅇ늘 정의해두고 사용하는 SELECT문 등이 이에 해당된다.

이 인라인 뷰와 ROWNUM을 사용하면 ORDER BY절을 통해 정결된 결과 중 최상위 몇 개 데이터만을 출력하는 것이 가능하다.

 

 

 

 

 

 

 

 

 

 

ROWNUM을 추가로 조회하기

ROWNUM은 의사 열이라고 하는 특수 열이다. 의사 열은 데이터가 저장되는 실제 테이블에는 존재하지 않지만 특정 목적을 위해 테이블에 저장되어 있는 열처럼 사용 가능한 열을 뜻한다.

이 결과를 살펴보면 ROWNUM 열은 EMP 테이블에 존재하지 않지만 열의 데이터가 숫자로 출력되고 있다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

인라인 뷰로 TOP-N 추출하기

급여가 높은 상위 세 명의 데이터만 출력하려면 ROWNUM을 WHERE절 조건으로 지정하면 된다.

---

 

 

 

학습을 마치고

아직 인덱스와 시퀀스 단원이 남아있는데 일단 여기까지 학습을 마쳐본다. 뷰는 오늘 아침에 정처기에서 학습했던 부분이라 더 관심이 가기도 했다. 이론만 학습해서 무슨 말인가 했었지만 이젠 사용할 수 있게 되어 데이터베이스를 공부하는 일이 더 즐겁게 느껴진다.

 

5시에는 13장 학습을 다 마치려고 했는데, 권한도 부여해주어야 하고 모르는 부분을 찾아서 학습하다 보니 생각보다 시간이 더 많이 걸렸다. 그래도 새로운 내용을 알게 되고 사용할 수 있게 되어 정말 감사했다. 무엇보다도 잘 안될 때도 포기하지 않고 끝까지 문제를 해결하려고 하는 나의 마음가짐에 만족스러웠다.
다음 포스트에서 13장의 마지막 개념 학습인 인덱스와 시퀀스에 대해서 학습해볼 것이다~