본문 바로가기

.주제별/SQL,DB

SQL)강의- 제 10 장 Oracle Data Dictionary

출처 : http://naver.kaist.ac.kr/oracle_sql/s10.htm

 

1. 오라클 데이터베이스의 테이블

개요

    ◈Oracle 데이터 사전은 데이터베이스가 만들어지면 자동으로 생성된다.
    ◈데이터베이스에 대한 작업이 있으면 Oracle7 server가 데이터 사전을 갱신하고 유지보수한다.
    ◈모든 데이터 사전 테이블은 SYS사용자가 소유한다.
       이러한 테이블의 정보는 사용자가 이해하기 어렵기 때문에 직접 엑세스하는 경우가 드물다.
    ◈따라서 데이터 사전의 정보는 사용자가 이해하기 쉽게 데이터 서전 뷰라는 형태로 제공된다.
     

Oracle7 데이터베이스 테이블

    테이블

    설    명

    사용자 테이블

    사용자가 만들고 사용하는 테이블
    사용자가 입력한 정보를 저장한다.

    Data Dictionary

    Oracle이 만들고 관리하는 테이블의 집합
    데이터베이스에 대한 정보를 저장하고 있다.
      ① Oracle Server에 Login할 수 있는 USER명
      ② 사용자에게 허가된 권한
      ③ 데이터베이스 객체명
      ④ 테이블 제약조건
      ⑤ Auditing 정보
    데이터 사전은 모든 데이터베이스 사용자가 참조하기 위한 것이다.
    데이터베이스는 데이터 사전에 따라 자체 정보를 기록하고 검사하기 때문에 데이터 사전은 Oracle7 Server의 작업에 매우 중요하다.

     

데이터 사전에 대한 Query

    ◈SQL SELECT문을 써서 데이터사전을 Query할 수 있다.
    ◈권한에 따라 여러가지 뷰를 Query를 할 수 있다.
    ◈뷰의 종류

      접두어

         

      USER_

      사용자 소유의 객체에 관한 정보를 저장하고 있다.

      ALL_

      사용자에게 액세스가 허용된 객체에 관한 정보를 저장하고 있다.

      DBA_

      DBA권한을 가진 사용자가 액세스할 수 있는 정보를 저장하고 있다.

      V$

      서버의 성능과 Locking에 관한 정보등을 가지고 있다.
      일반적으로 DBA에게만 허용된다.

      : 다른 몇가지 dictionary 뷰는 위에 나열된 접두어를 쓰지 않고 synonym을 사용한다.

      뷰 이름

      설    명

      Dictionary

      모든 데이터 사전의 테이블, 뷰, synonym을 display

      IND

      USER_INDEXES의 synonym


    ♠ 예제 : Dictionary뷰는 사용자가 액세스할 수 있는 모든 dictionary뷰를 주석과 함께 나열한다.
                또한 뷰에대한 Synonym인 DICT을 사용할 수도 있다.

    SQL> SELECT * FROM DICTIONARY;

     

    ♠ 예제 : SQL*Plus DESCRIBE 명령을 써서 Dictionary 뷰의 구조를 Display할 수 있다.
                USER_OBJECTS의 구조를 출력하라.

    SQL> DESC user_objects

     

    ♠ 예제 : Dictionary 테이블과 뷰의 column에 대한 정보를 보고자 한다면 DICT_COLUMNS뷰를
                참조하라.

    SQL> SELECT column_name, comments
              FROM   dict_columns
              WHERE  table_name = 'USER_OBJECTS';

     

    ♠ 예제 : 사용자 소유의 객체 유형을 출력하라.

    SQL> SELECT DISTINCT object_type   FROM   user_objects;

     

    ♠ 예제 : Dictionary뷰의 COMMENTS column에 대한 query를 하여 특정 주제에 대한 Dictionary
                View를 찾을 수 있다. Grant라는 키워드를 갖고 있는 모든 Dictionary 뷰를 찾아보아라.

    SQL> SELECT * FROM dictionary
              WHERE  LOWER(comments) LIKE '%grant%';

     

2. 테이블의 제약조건 확인

    ◈모든 제약조건 정의와 이름을 보고자 한다면 USER_CONSTRAINTS를 참조하라.
    USER_CONS_COLUMNS 뷰에서 제약조건 이름과 관련된 column의 정보를 확인할 수 있다.
    ◈이 뷰는 제약조건 명을 시스템이 정한 경우에 특히 유용하다.

    실습 : 앞에서 작성한 TEST1 테이블에 대한 제약조건을 DESC 명령으로 확인하라.

    SQL> DESC test1;

     

    ◈ DESC 명령은 NOT NULL column만 확인될 뿐이다.
     

    실습 : TEST1 테이블에 대한 제약조건을 USER_CONS_COLUMNS 뷰로 확인하라.

    SQL> SELECT constraint_name, constraint_type, search_condition
             FROM user_constraints
             WHERE table_name = 'TEST1';

     

    ◈ TABLE_NAME의 값은 반드시 대문자로 입력하여야 한다.
    실습 : TEST2 테이블에 대한 제약조건을 확인하여 위 TEST1 테이블과 비교하여 보라.

    SQL> SELECT constraint_name, constraint_type, search_condition
              FROM user_constraints
              WHERE table_name = 'TEST2';