여행을 개발하다
[Oracle] Data Dictionary 본문
오늘은 Oracle Data Dictionary에 대해 포스팅하고자 한다.
처음 Data Dictionary에 대해 들었을 때는 프로시저나 함수, 트리거와 같이 무엇인가 아주 복잡한 개념으로만 생각했다. 그런데 말 그대로 백과사전이라는 뜻이었고, 오라클 데이터베이스에 대한 수많은 정보를 제공하는 역할을 하고 있었다.
'데이터 사전'이라 불리는만큼, 다수의 테이블이 포함되어 있는 데이터베이스를 핸들링 할 때 꽤나 유용하게 사용하고 있다.
1. Data Dictionary란?
- 데이터 사전(Data Dictionary)이란 대부분 읽기전용으로 제공되는 테이블 및 뷰들의 집합으로 데이터베이스 전반에 대한 정보를 제공 한다.
- 오라클 데이터베이스는 명령이 실행 될 때 마다 데이터 사전을 Access 한다.
- DB작업동안 Oracle은 데이터 사전을 읽어 객체의 존재여부와 사용자에게 적합한 Access 권한이 있는지를 확인한다. 또한 Oracle은 데이터 사전을 계속 갱신하여 DATABASE 구조, 감사, 사용자권한, 데이터등의 변경 사항을 반영 한다.
데이터 사전에 저장되는 내용은 아래와 같다.
- 오라클의 사용자 정보
- 오라클 권한과 롤 정보
- 데이터베이스 스키마 객체(TABLE, VIEW, INDEX, CLUSTER, SYNONYM, SEQUENCE..) 정보
- 무결성 제약조건에 관한 정보
- 데이터베이스의 구조 정보
- 오라클 데이터베이스의 함수 와 프로지저 및 트리거에 대한 정보
- 기타 일반적인 DATABASE 정보
출처 :http://www.gurubee.net/lecture/1520(꿈꾸는 개발자 DBA 커뮤니티)
단순히 데이터에 대한 정보(메타데이터라고 해야하나)를 제공하는 줄만 알고 있었는데, 생각보다 그 역할이 중요했다. 접속중인 사용자에 대한 데이터베이스 권한 체크에서부터, 실시간 데이터 정보 갱신까지.
일단 SQL을 날리면 반드시 Dictionary를 거친다고 하니, Oracle 자체의 연동 및 의존도도 상당히 높아보인다. 그래서 함부로 Insert, Update, Delete를 할 수 없으며, 그 특징에 맞게 View 형태로 제공된다(Dictionary를 좀 더 정확히 말하면 Data Dictionary View라고 부름). 사용자 입장에서는 필요한 정보만 탐색용(Select)으로만 사용하면 된다. 모든 Dictionary는 read-only이며, 시스템에 의해 관리되고 있다.
2. Dictionary의 종류
백과사전인만큼 담고 있는 정보도 다양한데, 어떤 정보를 담고 있느냐에 따라 다시 여러 종류로 나뉜다.
내 경험에 빗대어 보면, 아버지가 어렸을 적에 권수가 20권 정도되는 백과사전을 사주셨었다. 책 한 권당 경제/사회, 생물, 자연과학 등으로 그 주제가 정해져 있었고, 나는 궁금한 부분이 어디인가에 따라 다른 책을 꺼내보았다.
오라클 DB에서도 마찬가지이다. 내가 얻고자 하는 것이 컬럼에 대한 정보인지, 테이블에 대한 정보인지, 프로시저에 대한 정보인지를 정한다. 그리고 그 정보가 포함된 권을 꺼내본다고 생각하면 이해하기 쉽다.
어렸을 적 백과사전이 1권부터 20권까지 있었다면, 오라클에서는 몇 권의 백과사전이 있을까. 종류를 조회하기 위해서는 다음과 같이 SQL을 날려본다.
SELECT * FROM DICTIONARY;
위 쿼리는 조회할 수 있는 모든 Dictionary의 리스트를 보여준다. 단, 접속중인 사용자가 DBA냐, 일반 사용자냐 등에 따라 조회할 수 있는 Dictionary의 수가 달라진다. 모든 사용자가 조회할 수 있는 Dictionary가 있는 반면, 관리자만 조회할 수 있는 Dictionary도 있는 것이다. 그래서 때에 따라 관리자가 사용자에 따라 특정 Dictionary에 대한 열람 권한을 부여할 수도 있다.
TABLE_NAME COMMENTS
------------------ -----------------------------------------------------------------
USER_CONS_COLUMNS Information about accessible columns in constraint definitions
ALL_CONS_COLUMNS Information about accessible columns in constraint definitions
USER_LOG_GROUP_COLUMNS Information about columns in log group definitions
ALL_LOG_GROUP_COLUMNS Information about columns in log group definitions
USER_LOBS Description of the user's own LOBs contained in the user's own tables
ALL_LOBS Description of LOBs contained in tables accessible to the user
USER_CATALOG Tables, Views, Synonyms and Sequences owned by the user
ALL_CATALOG All tables, views, synonyms, sequences accessible to the user
USER_CLUSTERS Descriptions of user's own clusters
ALL_CLUSTERS Description of clusters accessible to the user
USER_CLU_COLUMNS Mapping of table columns to cluster columns
USER_COL_COMMENTS Comments on columns of user's tables and views
ALL_COL_COMMENTS Comments on columns of accessible tables and views
USER_COL_PRIVS Grants on columns for which the user is the owner, grantor or grantee
ALL_COL_PRIVS Grants on columns for which the user is the grantor, grantee, ...
USER_COL_PRIVS_MADE All grants on columns of objects owned by the user
ALL_COL_PRIVS_MADE Grants on columns for which the user is owner or grantor
USER_COL_PRIVS_RECD Grants on columns for which the user is the grantee
....... ....................T
ABLE_NAME은 해당 Dictionary의 이름, 그리고 COMMENTS는 그 테이블이 어떤 정보를 담고 있는지에 대한 설명이다. 예컨데, 컬럼에 대한 정보를 보고 싶다면 COMMENTS에만 조건을 걸어서 리스트를 볼 수도 있다.
SELECT * FROM DICTIONARY WHERE COMMENTS LIKE '%COLUMN%'; TABLE_NAME COMMENTS
-------------------------- ------------------------------------------------------
ALL_IND_COLUMNS COLUMNs comprising INDEXes on accessible TABLES
USER_IND_COLUMNS COLUMNs comprising user's INDEXes and INDEXes on ...
ALL_SUBSCRIBED_COLUMNS Synonym for USER_SUBSCRIBED_COLUMNS
COLS Synonym for USER_TAB_COLUMNS
ALL_PUBLISHED_COLUMNS Synonym for USER_PUBLISHED_COLUMNS
현재 내가 사용하고 있는 버전과 접속한 계정의 권한 범위 내의 경우, 총 804개의 데이터 사전이 있다. 버전이 올라가면 올라갈수록 데이터 사전의 수는 변경될 수 있으며, 갱신되는 Dictionary에 대한 정보는 아래 URL을 참고하면 된다.
https://docs.oracle.com/cd/B28359_01/nav/catalog_views.htm
3. Data Dictionary의 명명규칙
오라클에서는 Dictionary의 이름을 붙이는데는 나름대로의 규칙이 있다. 대체로 이름만 봐도 '이런 정보를 담고 있구나!'고 유추해볼 수 있다. 다음은 대표적인 Dictionary의 명명 규칙들이다.
3-1. USER_XXX
- 사용자가 사용하고 있는 Object와 관련된 정보.
ex)USER_ADDM_FDG_BREAKDOWN, USER_ADDM_FINDINGS, USER_ADDM_INSTANCES, ...
3-2. ALL_XXX
-현재 사용자가 접근이 가능한 Object에 관련된 정보.
ex)ALL_ALL_TABLES, ALL_APPLY, ALL_APPLY_CHANGE_HANDLERS, ...
3-3. DBA_XXX
- DBA 권한을 가지고 있는 사용자만이 접근할 수 있는 정보.
ex) DBA_OBJECTS, DBA_INDEXES, DBA_TABLES, ...
3-4. V$_XXX
- 서버의 성능이나 시스템관련 정보, 메모리, Lock 등에 관한 정보.
ex )V$ACTIVE_INSTANCES, V$ACTIVE_SESS_POOL_MTH, V$ADVISOR_CURRENT_SQLPLAN, ...
4. Data Dictionary 사용 예제
Data Dictionary가 실무에서 가장 유용하게 빛을 발했을 때는 테이블 정의서를 만들 때였다.
프로젝트를 시작하면 요구사항 분석 후 바로 테이블 설계에 들어갔는데, 안타깝게도 초기에 설계된 테이블이 변경없이 그대로 가는 경우가 드물었다. 필요에 따라 컬럼의 삭제, 추가, 수정이 불가피했기 때문이다. 그래서 팀원들과 변경사항을 적시에 공유해야만 했다. 이 때, 테이블 이름만 변수로 지정해서 SELECT 하는 쿼리를 만들면 좋겠다는 생각을 매번 했었는데, 그 솔루션이 Data Dictionary였다.
테이블 정의서에 언급된 내용들은 프로젝트별로 다르겠지만, 다음 항목들에서 크게 벗어나지 않는다.
테이블명, 컬럼명, 컬럼 자료형, NULL 가능여부, PK(Primary Key) 여부, Column Description
* 테이블 정의서 만들기
① 사용할 Dictionary
*USER_TAB_COLUMNS (사용자의 컬럼 정보 조회)
* USER_CONSTRAINTS (사용자가 정의한 테이블의 제약조건)
* USER_CONS_COLUMNS (사용자가 컬럼에 정의한 제약조건)
* USER_COL_COMMENTS (사용자가 컬럼에 정의해놓은 커멘트)
② 테이블, 컬럼명, 컬럼에 대한 커멘트는 그대로 가져온다.
③ 컬럼 자료형은 VARCHAR2일 경우 길이를 함께 명시해준다.
④ NULL 가능여부와 PK(Primary Key) 여부는 해당할 경우 'Y'로 아닐 경우 공백으로 표시한다.
SELECT UTC.TABLE_NAME,
UTC.COLUMN_NAME,
CASE WHEN UTC.DATA_TYPE IN ('VARCHAR2')
THEN UTC.DATA_TYPE || '(' || UTC.DATA_LENGTH || ')'
ELSE UTC.DATA_TYPE
END AS DATA_TYPE,
CASE WHEN UTC.NULLABLE = 'Y' THEN 'Y'
ELSE ''
END AS NULLABLE,
CASE WHEN T1.COLUMN_NAME IS NOT NULL THEN 'Y'
ELSE ''
END AS IS_PK,
UCCM.COMMENTS
FROM USER_TAB_COLUMNS UTC
LEFT OUTER JOIN
(SELECT DISTINCT COLUMN_NAME
FROM USER_CONSTRAINTS UC
INNER JOIN
USER_CONS_COLUMNS UCC
ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P'
) T1
ON UTC.COLUMN_NAME = T1.COLUMN_NAME
LEFT OUTER JOIN
USER_COL_COMMENTS UCCM
ON UTC.TABLE_NAME = UCCM.TABLE_NAME
AND UTC.COLUMN_NAME = UCCM.COLUMN_NAME
WHERE UTC.TABLE_NAME = '테이블 이름';
다음은 위 SQL을 사용하여 'ACADEMY'라는 테이블에 대한 정의서를 출력한 내용이다. 대부분의 데이터베이스 관리 Tool은 SELECT 결과 복사 기능을 지원하기 때문에, 엑셀 파일에 쉽게 붙여넣을 수 있다.
TABLE_NAME COLUMN_NAME DATA_TYPE NULLABLE IS_PK COMMENTS
-- ----------- ----------- ----------- ---------- -------- ------------------
1 ACADEMY SABUN VARCHAR2(10) Y
2 ACADEMY SEQ NUMBER Y
3 ACADEMY DEGREE VARCHAR2(30) Y
4 ACADEMY MAJOR VARCHAR2(30) Y
5 ACADEMY GRAD_YEAR VARCHAR2(6) Y
6 ACADEMY SCH_NM VARCHAR2(30) Y
지금까지 Oracle의 Data Dicationary(View)와 이를 활용한 테이블 정의서 SQL 작성 예제에 대해 알아보았다. 감사합니다 : )
'데이터베이스 > Oracle' 카테고리의 다른 글
| [Oracle] 계층형 쿼리 - START WITH ~ CONNECT BY 구문 (1) | 2020.03.08 |
|---|---|
| [Oracle] XMLELEMENT, XMLAGG 함수 - 다수 레코드 1열로 가져오기 (0) | 2020.03.07 |
