전문가칼럼

DBMS, DB 구축 절차, 빅데이터 기술 칼럼, 사례연구 및 세미나 자료를 소개합니다.

오라클 DICTIONARY를 활용한 DB툴 프로그램 ‘FreeSQL’

전문가칼럼
DBMS별 분류
Oracle
작성자
dataonair
작성일
2015-07-30 00:00
조회
14028




◎ 연재기사 ◎


물탱크 구조로 알아본 오라클의 블록 옵션 ‘PCTFREE와 PCTUSED’


이산가족 찾기 생방송을 통해 배우는 DB 원리


개발자에게 맞는 DB 공부방법 찾기: 물리적 분류와 논리적 분류 그리고 인덱스


데이터베이스 인덱스의 오해와 진실


쉬운 것이 올바른 것이다. ‘인덱스 끝장리뷰’ (상)


쉬운 것이 올바른 것이다. ‘인덱스 끝장리뷰’ (하)


누구도 알려주지 않았던 ‘오라클 인덱스 생성도’의 비밀


누구도 알려주지 않았던 ‘오라클 쿼리 작성의 비법’


퀴리 최적화 및 튜닝을 위한 오라클 공정쿼리 작성법


만능 쿼리와 한 방 쿼리


오라클 옵티마이저 ‘CBO와 RBO’ 이해하기


재미있는 DB 이야기 ‘60갑자와 쿼리’


그림으로 배우는 ‘오라클 조인의 방식’ 이야기


반드시 알아야 하는 오라클 힌트절 7가지


오라클 플랜을 보는 법


개발자들의 영원한 숙제 ‘NULL 이야기’


알면 유용한 오라클 기능 ‘GATHER_PLAN_STATISTICS’


알면 유용한 오라클 기능들


오라클 DICTIONARY를 활용한 DB툴 프로그램 ‘FreeSQL’


이제는 말할 수 있다: 주식 자동매매 프로그램(상)


이제는 말할 수 있다: 주식 자동매매 프로그램(하)


개발자들이 자주 접하는 오라클 에러 메세지


재미있는 DB 이야기 ‘사라진 날짜를 찾아라’


오라클 랜덤 함수와 사용자 정의 함수


그림으로 배우는 ‘공정쿼리와 인덱스 생성도’


이병국의 개발자를 위한 DB 이야기: 디폴트 세팅의 함정과 오라클 파라미터


재미있는 DB 이야기 ‘놀라운 마방진의 세계’


오라클 운반 최소 단위 BLOCK


이병국의 개발자를 위한 DB 이야기: 이세돌과 알파고의 세기의 대결


이병국의 개발자를 위한 DB 이야기(30회) : DB 엔지니어의 가볍게 읽는 세상 이야기


이병국의 개발자를 위한 DB 이야기: 튜닝(31회) : 개발자를 위한 DB 튜닝 실전(1편)


이병국의 개발자를 위한 DB 이야기: 튜닝(32회) : 개발자를 위한 튜닝 실전(2편)


이병국의 개발자를 위한 DB 이야기: 튜닝(33회) : 개발자를 위한 튜닝 실전(3편)


이병국의 개발자를 위한 DB 이야기: 튜닝(34회) : 개발자를 위한 DB 튜닝 실전(4편)


이병국의 개발자를 위한 DB 이야기: 튜닝(35회) : 개발자를 위한 튜닝 실전(5편)


이병국의 개발자를 위한 DB 이야기: 페이징 처리에 대한 이해 (36회)


보기 좋은 떡이 먹기도 좋다 - 좋은 쿼리 좋은 성능


테이블의 수직분할과 수평분할에 대한 이해


DB 성능 제고를 위한 채번의 이해와 방식별 장단점 비교


이병국의 개발자를 위한 DB 이야기: 마지막회 : ‘개발자를 위한 DB 이야기’ 연재를 마치며



이병국의 개발자를 위한 DB 이야기: 프로그램 (19회)

오라클 DICTIONARY를 활용한 DB툴 프로그램 ‘FreeSQL’



성공과 실패의 경험을 나누자, 용기와 희망을 나누자

개발업무를 시작으로 IT계에 입문했던 필자가 10년 가까이 DB 엔지니어로서 활동하면서 얻은 경험과 지식을 나누고자 한다. DB를 자주 접하는 SW 개발자뿐 아니라, DB 전문가를 꿈꾸는 대학생에서 DB 분야에 입문한 지 1~2년 된 기 입문자가 쉽게 이해할 수 있도록 비유를 통해 쉽게 접근해볼 계획이다. 물론 전문가들이라도 다시 한번 개념을 정립하는 의미에서 필요한 내용이 될 수 있다.

전체적으로 DB의 기본 원리와 개념을 이해하고 테이블, 인덱스, 쿼리, 튜닝, 플랜 등 개발자들이 알아야 하는 DB 전분야에 대해 쉽게 이해하도록 설명하겠다. DB 기술서적이나 번역서보다는 조금 더 부드럽게 접근할 계획이다. 그렇다고 흔히 서점에서 만날 수 있는 개발자 위주의 SQL 소개서도 아니다. 이 연재는 시리즈로 나갈 것이다. 연재를 끝까지 읽는 독자라면, 준전문가 수준의 DB 원리를 아는 것을 목표로 한다.



씨줄과 날줄

나이를 먹을수록 새로 만나는 사람에 대한 두려움은 누구나 갖게 마련이다. 그래서 처음 만나는 자리에서 상대방의 나이를 물어보고, 동향인지, 동문인지, 어떤 그룹에 속하는지에 대한 질문이 먼저 나온다. 하나라도 일치하면 비로소 동질감을 느끼고 편안하게 다음 대화로 넘어 갈 수 있다. 결코 개인적인 성향이나 취미에 관한 내용이 먼저 나오는 법이 없다. 이는 우리 사회가 개인보다는 조직에 더 익숙해져 있기 때문일 것이다.

column_img_1988.jpg

▲김홍도 ‘길쌈’

지금까지 IT 업계에서도 이와 크게 다르지 않았다. 새로운 프로젝트에 가면, 늘 예전에 어떤 프로젝트에 있었다는 이야기부터, 누구 누구와 같이 일했다는 것을 말하면서 아는지 물어본다. 결국은 서로 동질감을 느끼는 접점을 찾았었다. 그런데 요즘 젊은 친구들은 많이 다른 거 같다. 이러한 주제로 동질감을 찾기 보다는 자기가 좋아하는 프로그램은 무엇인지, 앞으로 어떠한 것을 배우고 싶다든지, 그런 미래 지향적인 대화 내용들이 더 많다. 조직이라는 울타리 보다는 개인적인 자유로움을 더 추구하는 시대의 흐름 때문이기도 할 것이다.

옷감을 짤 때 가로 방향으로 엮는 줄을 씨줄이라 하고, 세로 방향으로 엮는 줄을 날줄이라 한다. 씨줄만으로 혹은 날줄만으로 옷감을 만들 수는 없다. 씨줄과 날줄로 잘 엮어진 옷감은 튼튼하고 오래 사용할 수 있다.

우리는 사회 구성원으로 서로 관계를 맺고 있다. 수많은 관계와 관계 속에서 씨줄과 날줄로 엮여 있다. 아무리 좋은 개인적인 능력을 가졌더라도 튼튼한 조직이 받쳐주지 않으면 성공이 쉽지 않을 것이고, 아무리 튼튼한 조직이라도 개인적인 능력을 무시하는 조직이라면 결코 발전이 없을 것이다. 조직이라는 씨줄과 능력이라는 날줄이 잘 엮여야만 비로소 지속 가능한 발전이 있다고 생각한다.

씨줄과 날줄을 이야기하는 이유는, 오라클에서 딕디셔너리 가 바로 씨줄과 날줄처럼 중요한 요소이기 때문이다. 씨줄과 날줄을 잘 삼아야 좋은 옷감이 나오듯이 딕셔너리에 대한 이해와 활용은 당신의 능력을 배가 시킬 것이다. 이번 연재의 내용은 바로 딕셔너리에 관한 것이다.

이번 연재를 포함하여 2회에 걸쳐서 필자가 예전에 개발한 프로그램을 소개하고자 한다. 이번 연재 내용은 오라클 딕셔너리를 활용한 DB 프로그램에 관한 것이고, 다음 연재 내용은 주식자동매매 프로그램에 관한 것이다. 필자의 글을 읽는 사람은 전문 DBA보다는 DB에 관심 있는 개발자들이 더 많을 것이라고 보고 이렇게 주제를 정해 보았다. 물론 오라클 DB와도 밀접한 관련성이 있기 때문이기도 하다.



Oracle DICTIONARY

DBMS를 관리하기 위한 정보들을 가지고 있는 테이블의 집합을 딕셔너리(DICTIONARY)라 한다. 오라클은 딕셔너리를 통해서 우리에게 수많은 정보를 제공하고 있다. 딕셔너리는 오라클 시스템에 대한 정보의 보고이며, 데이터 사전이라 부르기도 한다.

사용자 소유의 모든 오브젝트 정보를 알고자 한다면 다음과 같이 조회 하면 된다.
SELECT * FROM USER_OBJECTS

사용자 소유의 테이블 정보를 알고자 한다면 다음과 같이 조회한다.
SELECT * FROM USER_TABLES

현재 시점의 세션 정보를 알고자 한다면 다음과 같이 조회한다.
SELECT * FROM V$SESSION

그렇다면 이와 같은 DB 시스템 정보를 제공하는 수많은 딕셔너리는 어떻게 알 수 있을까 바로 다음과 같이 조회하면 된다.

SELECT * FROM DICTIONARY
혹은
SELECT * FROM DICT

---------------------------------------------------------------------------------------------
TABLE_NAME COMMENTS
----------------------------------------------------------------------------------------------
USER_CUBES OLAP Cubes owned by the user in the database
… …
USER_TABLES Description of the user's own relational tables
… …
USER_TRIGGERS Triggers having FOLLOWS or PRECEDES ordering owned by the user
… …
----------------------------------------------------------------------------------------------



현재 오라클은 딕셔너리를 통하여 700여 개 이상의 시스템 정보를 제공하고 있다. 오라클이 제공하는 모든 시스템 정보를 알 필요는 없지만, 그 중에서 우리가 필요로 하는 부분은 다음과 같이 조회하여 찾아서 사용하면 된다.

SELECT * FROM DICTIONARY
WHERE TABLE_NAME LIKE ‘%INDEX%’ → 값은 대문자로 입력해야 함에 유의하자.

------------------------------------------------------------------------------------
TABLE_NAME COMMENTS
-------------------------------------------------------------------------------------
ALL_INDEXES Descriptions of indexes on tables accessible to the user
ALL_INDEXTYPES All indextypes available to the user
ALL_INDEXTYPE_ARRAYTYPES All array types specified by the indextype
ALL_INDEXTYPE_COMMENTS Comments for user-defined indextypes
ALL_INDEXTYPE_OPERATORS All operators available to the user
USER_PART_INDEXES
ALL_PART_INDEXES
USER_INDEXES Description of the user's own indexes
USER_INDEXTYPES All user indextypes
USER_INDEXTYPE_ARRAYTYPES All array types specified by the indextype
USER_INDEXTYPE_COMMENTS Comments for user-defined indextypes
USER_INDEXTYPE_OPERATORS All user indextype operators
INDEX_HISTOGRAM statistics on keys with repeat count
INDEX_STATS statistics on the b-tree



오라클 딕셔너리는 크게 다음과 같은 접두어로 분류해 볼 수 있다.

ALL_* : 현재 오라클에 접속한 사용자가 접근 가능한 모든 정보들에 대한 딕셔너리
USER_* : 현재 오라클에 접속한 사용자가 소유하고 있는 모든 정보들에 대한 딕셔너리
DBA_* : 관리자 계정으로 접속한 사용자가 조회 가능한 모든 정보들에 대한 딕셔너리
V$* : Dynamic Performance View라고도 하며, DBA의 모니터링에 많이 이용되는 딕셔너리
이처럼 접속한 계정이 관리자 계정인지 사용자 계정인지에 따라 조회 가능한 딕셔너리가 구분된다.



오라클 딕셔너리를 이용한 DB 툴 개발 ‘FreeSQL’

우리는 딕셔너리 정보를 갖고 많은 부분에 활용할 수 있다. 딕셔너리에서 제공하는 DBMS 객체 정보 및 상태 정보를 이용하여 만든 것이 바로 Toad, Orange와 같은 DB 툴이다. 이러한 DB 툴은 사용자가 더 편하게 사용할 수 있게 직관적인 인터페이스를 제공한다. 또한 다중 쿼리에 대한 탭을 제공하며, 각종 오브젝트에 대한 정보 제공 및 헬스 체크 기능을 실시간으로 제공하기도 한다.

지금은 DB 관련 직무를 수행하고 있지만, 불과 몇 년 전까지 필자는 SI 프로젝트를 수행했다. 그 당시 프로젝트마다 매번 바뀌는 DB에 대해 호기심에 이끌려 많이 배우기도 했지만, 때론 곤혹스러운 경우도 많았다. 같거나 유사한 명령어들 사이에서 헷갈리기도 하였고, 각각의 DB에 맞는 DB툴을 구해 설치해야 했고, 또 그 사용 방법을 익혀야 했었다.

당시 그러한 불편함을 해소하고자 개인적으로 만든 프로그램이 바로 FreeSQL이다. FreeSQL은 오라클, MSSQL, MDB 등 어떤 종류의 DB에서도 사용할 수 있게끔 범용적으로 만들고자 하였다.

개인적인 호기심과 프로젝트 수행 도중 시간이 날 때마다 틈틈이 개발한 것이라 완성도는 비록 떨어졌지만 비상업용으로 사용하기엔 부족함이 없었다.

개발 언어 : 비주얼 베이직
내부 디비 : MDB
지원 디비 : DB2, MDB, MSSQL, MYSQL, 오라클, 큐브리드
개발 기간 : 3개월
제공 기능 : FreeSQL ………………… 토드나 오렌지와 같은 DB툴로서 다양한 DB 지원 가능
Books ……………………. 다양한 내용을 카테고리에 맞게 기록하는 사전식 메모 기능
Explorer ………………… 웹 브라우저 창을 두어 검색을 위한 서브 기능 지원
Cmd ……………………… DOS 모드의 작업을 수행하기 위한 서브 기능 지원
Chatting ……………….. 프로젝트에서 협업을 위한 채팅 서브 기능 지원

column_img_1989.jpg

[그림] DB 툴 개발도구 ‘FreeSQL’

전체적인 화면 구성은 위 그림과 같다.

. 우측 상단 프로그램 영역에서 FreeSQL, Books, Explorer, Cmd, Chatting을 선택할 수 있다.
. FreeSQL 선택 시 화면 좌측에 DB 서버 트리와 객체 정보를 볼 수 있는 객체 트리가 있다.
. 최상단 메뉴바에는 DB 관련 그룹을 등록하거나 DB 연결 정보를 저장하는 메뉴가 있다.
. 화면 우측 쿼리창에서 쿼리를 작성할 수 있고, 그 위에 문서 작성에 필요한 기능버튼이 있다.

column_img_1990.jpg

[그림] DB 툴 개발도구 ‘FreeSQL’ 세부 메뉴

좀더 구체적으로 살펴보면, 화면 좌측의 서버 트리와 객체브라우저 트리의 모든 정보는 딕셔너리 정보를 이용하였다. 주로 이용한 딕셔너리는 다음과 같다.

ALL_TAB_COLS
ALL_TAB_COLUMNS
ALL_TAB_COMMENTS, … 등의 테이블 관련 딕셔너리

ALL_INDEXES
ALL_IND_COLUMNS
ALL_IND_STATISTICS, … 등의 인덱스 관련 딕셔너리

ALL_OBJECTS
ALL_DB_LINKS
ALL_TRIGGERS, … 등의 각종 오브젝트 관련 딕셔너리

그 외에 V$LOCK, V$SESSION 등으로 DB 상태 정보를 나타내는 각종 딕셔너리를 사용하였다.

또한 화면 우측의 쿼리창에서 쿼리를 작성할 수 있고, 그 바로 위에 문서 에디터에 필요한 기능버튼이 있다. 그리고 쿼리 구문에 사용된 명령어의 특성에 따라 색깔로 구분되도록 하였다.

SELECT, FROM, WHERE, AND, ORDER BY, 등의 쿼리 명령어는 파랑색으로 표시
SUBSTR, NVL, MAX, MIN, AVG, 등의 함수는 주황색으로 표시
그 외에 Books, Cmd 등의 프로그램 화면 구성은 아래와 같다.

column_img_1991.jpg

이번 연재는 애초에 프로그램을 소개하려는 목적이 아니었다. 오라클 딕셔너리에 대한 내용 설명과 그 활용에 대한 무궁무궁진한 가능성을 알리고 싶었다. 딕셔너리를 사용해서 구현 가능한 프로그램은 매우 많다. 쿼리 작성, 성능 진단, 튜닝 분석, 등 각종 DB 관련 프로그램을 개발할 수 있다. 이러한 개발에는 DB만을 전문으로 하는 사람보다는, DB에 관심이 많은 여러분과 같은 개발자들이 더 유리하다고 생각한다. 새로운 DB 프로그램 개발의 주인공은 바로 당신이 될 수 있다.

다음 연재는 뉴스-공시를 이용한 주식자동매매 프로그램에 관한 내용이다. 주식 매매를 해 본 사람이라면 ‘단타, 초단타’라는 주식 용어를 알 것이다. 단타, 초단타도 아닌 극초단타 주식매매 프로그램에 관한 내용을 설명하고자 한다. 분석-설계의 ERD 단계부터 주식 자동매매 로직을 구현하는 개발 단계까지 설명할 것이다. 경험이 있는 보통의 개발자라면 충분히 주식자동매매 프로그램을 구현할 수 있는 수준으로까지 쉽게 설명할 것이다.



용기를 갖자
오라클 DB뿐 아니라 대부분의 DB 구성 알고리즘은 어느 날 ‘ 하늘에서 뚝 떨어져 새로 만들어진 것’이 아니라 실생활에서 이용되는 혹은 이미 상식 수준에서 인지되는 그런 보편적인 원리를 바탕으로 만들어졌으므로이 ‘레몬시장이론’을 상기하며 DB를 지레짐작으로 어려워하지 말고 용기를 내고 하나씩 터득해 나가기를 바란다.

이 글은 DB 전문가 수준의 이해를 요구하지는 않는다. 단지 DB에 대해서 더 친숙하고 더 쉽게 이해하고 접근하길 바랄 뿐이다. 이 글을 읽으면서 궁금하거나 의문 나는 점이 있으면, 댓글을 달아주실 것을 적극 바란다. 아무리 어렵고 힘든 일이더라고 ‘관계’와 ‘소통’으로 풀어나갈 수 있음을 다시 한 번 믿으며...



[지난 문제의 정답과 풀이]

원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제



지난 연재에 출제한 ‘원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제’에 대한 정답과 해설은 아래와 같다. 문제를 풀면서 DB 원리를 하나씩 배우고 이해할 수 있다.



column_img_1992.jpg

column_img_1993.jpg

[이번 호 문제]

원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제



각 연재의 말미에 간단하면서도 재미있고 생각해 보는 문제를 출제하려 한다. 모든 문제는 DB의 원리를 이해할 수 있는 문제로 출제할 예정이다. 문제를 풀면서 DB 원리를 하나씩 배우고 이해할 수 있다. 정답과 그에 대한 설명은 다음 연재에서 한다.



column_img_1994.jpg