[SQL] DB안의 사전, 데이터 딕셔너리(Data Dictionary)!

 제가 SQL을 처음 배우면서 desc라는 명령을 알게 되었는데요, 그때 선생님께 물었습니다.
"테이블에 존재하는 컬럼들에 대해서는 이 명령어를 써서 알 수 있는데, 제가 만든 테이블들의 전체 정보를 알 수 없을까요?
무엇을 만들었는지, 알고 싶습니다."
 그랬더니 11장 예습하라고 하시던 선생님 생각이 납니다. 당시는 1,2장 배우고 있었던 때였죠.
아마, 얘기를 하시려던 부분이 데이터 딕셔너리(Data Dictionary) 파트 였을 겁니다.

 데이터 딕셔너리는 스키마 · 사용자 · 객체 · 권한 · 롤 · 데이터베이스의 정보 등등, 오라클 데이터베이스를 운영하는데 필요한 정보를 관리하는 별도의 객체들을 일컫습니다. 시스템 정보를 가지고 있기 때문에 (건드리면 위험) 보통, 뷰를 통해서 조회만 할 수 있습니다.

 딕셔너리에서 사용하는 뷰들을 딕셔너리 뷰라고 하는데, 이 딕셔너리 뷰는 시스템 뷰에 속합니다. 
이러한 데이터 딕셔너리 뷰는 데이터베이스 생성시에 오라클 시스템에 의해서 자동으로 생성됩니다.
SQL 수업이나 혹시 책을 참고 하셨다면 가끔 V$ ... 하는 것을 사용하는 것을 보셨을텐데요,
이를 동적 성능 뷰(Dynamic Performance View)라 하며 딕셔너리 뷰와 마찬가지로 시스템 뷰의 한 종류 입니다.

 참고로, 동적 성능 뷰는 메모리나 현재 세션에 관해서 정보를 알 수 있는 뷰입니다.

 이번글에서 알아볼 딕셔너리 뷰는 3종류로 나누어 볼 수 있는데요, ALL, DBA, USER로 시작하는 이름의 뷰입니다.
우선 구분을 짓자면 다음과 같습니다.

1. ALL : SQL에 접속중인 사용자에게 권한이 존재하는 모든 객체(Object)와 접근 가능한 객체에 대한 정보를 조회할 수 있습니다.
2. DBA : ALL과는 달리 DB 관리자들만 접속이 가능합니다. 모든 객체에 대해서 접근 가능합니다.
3. USER : SQL에 접속중인 사용자가 소유자인 객체의 정보를 조회할 수 있습니다.

 ALL과 관련해서 쓸수 있는 뷰는 다음과 같습니다.

1. ALL_OBJECTS
2. ALL_TABLES
3. ALL_INDEXES
4. ALL_SYNONYMS
5. ALL_SEQUENCE
6. ALL_VIEWS
...

 뷰가 엄청 많은데 위의 패턴들을 보시면 저런식으로 만들어지는 구나하고 유추해서 조회할 수 있겠고, 다음과 같은 방법으로도 알 수 있습니다.

SELECT owner, object_name, object_type
FROM all_objects
WHERE object_name LIKE 'ALL_%';

모든 것이 ALL_로 시작하는것에 착안한 방법입니다.

 그렇다면 hr 계정으로 접속해서 ALL뷰를 통한 객체에 대한 정보를 조회해 볼게요.
혹시나 특정컬럼을 조회하고 싶은데 무엇이 있는지 모르겠다라는 분들은 그냥 간단히 desc를 통해서 컬럼을 조회하면 되겠습니다.

SQL> desc all_objects;

 위와 같은 컬럼이 존재하는군요.
그렇다면 저는 hr계정과 관련된 객체들의 소유자(OWNER) 와 객체명(OBJECT_NAME), 객체타입(OBJECT_TYPE)에 대해서 조회하겠습니다.

SELECT owner, object_name, object_type
FROM    all_objects;

 다음과 같이 확인이 가능합니다.

-전체 결과의 일부입니다.

 보시면 소유자들이 전부 틀린데 우리는 시노님을 PUBLIC 으로 선언된 각각의 시노님을 통해서 쉽게 접근이 가능한 것이죠.
여기서 hr 이 소유한 테이블 정보만 보고 싶으면 WHERE 절을 통해서 다음과 같이 조회할 수 있습니다.

SELECT owner, object_name, object_type
FROM    all_objects
WHERE  object_type='TABLE' AND owner='HR';

 

참고로 where절에서 'TABLE'과 'HR' 처럼 대문자를 써주어야지 조회가 됩니다. 소문자로 했을시에는 조회가 되지 않습니다. 주의하세요.

 DBA와 USER도 위와 같은 방식으로 조회하고 사용할 수 있겠습니다. 하지만 DBA의 경우에는 관리자 권한으로 접속해야 사용 가능합니다.
USER는 접속한 사용자의 계정이 소유한 객체들만 조회할 수 있다는 점 명심하세요.

이것으로 글을 마칠게요. 다음에 복습할때는 DBA와 USER에 대해서 자세히 적도록 하겠습니다. 
아마 2-3주 내로 글을 덧붙일까합니다. 폭풍 공부하고 있어가지구요 ^^. 
공부한건.. 많은데.. 글은 참 더디네요. ㅋㅋ

도움 되셨다면 밑의 추천(손가락 표시)과 댓글 부탁드립니다.