데이터 딕셔너리는 오라클 데이터베이스의 중요한 부분입니다. 데이터 딕셔너리는 테이블과 뷰들의 집합이며 데이터베이스에 대한 정보를 제공합니다. 기본 데이터 딕셔너리 테이블은 데이터베이스를 생성할 때 sql.bsq 스크립트 파일을 수행하면 생성됩니다.
데이터 딕셔너리는 오라클 서버와 데이터베이스 관리자, 그리고 데이터베이스 사용자를 위한 정보의 중심 근원입니다.
데이터 딕셔너리는 DDL 명령이 수행될 때마다 오라클 서버가 갱신(update)합니다. 또한 테이블을 확장시키는 결과를 낳는 DML 명령도 데이터 딕셔너리를 갱신할 수 있습니다. 

 SYSTEM 테이블스페이스에 위치하며 SYS 사용자가 소유하고 있는 데이터 딕셔너리는 두 부분으로 이루어져 있습니다.
  
- 기본 테이블(base tables)
  - 데이터 딕셔너리 뷰(data dictionary views)

기본 테이블
기본 테이블, 또는 기초 테이블의 집합이 데이터 딕셔너리의 토대가 됩니다. 오라클 서버는 이러한 테이블들을 읽고 씁니다. 기본 테이블은 정규화(normalized)되어 있고 암호화(encoded)되어 있기 때문에 데이터베이스 사용자가 직접 접근하는 일은 거의 없습니다.
예를 들어 IND$ 테이블에 질의하여 데이터베이스 내에 정의된 인덱스에 대한 정보를 얻을 수도 있고 OBJ$ 테이블을 select하여 데이터베이스 내에 정의된 오브젝트를 출력할 수도 있습니다.
절대로 INSERT, UPDATE, 그리고 DELETE 등의 DML 명령을 사용하여 AUD$ 테이블을 제외한 기본 데이터 딕셔너리 테이블을 직접 갱신하지 마십시오.(“감사(Auditing)” 장을 참조하시오.)

데이터 딕셔너리 뷰
Catalog.sql 스크립트를 실행함으로써 생성됩니다.
뷰에는 기본 데이터 딕셔너리 테이블의 정보가 해독(decode)되고 요약되어 있습니다. 공용 동의어가 데이터 딕셔너리 뷰에 대해 정의되어 있어 뷰에 쉽게 접근할 수 있습니다.
대부분의 사용자는 기본 테이블 보다는 뷰에서 select하여 데이터 딕셔너리를 정보를 점검합니다.  


데이터 딕셔너리 뷰 범주
데이터 딕셔너리 뷰는 세 범주로 나뉘어집니다. 대부분의 경우 뷰는 서로 비슷한 정보를 가지고 있으며 접두어로 구분할 수 있습니다.

접두어 USER
어떤 사용자든지 접근할 수 있으며 일반적으로 사용자가 소유한 오브젝트에 대한 정보를 갖습니다. 예를 들어 USER_TABLES는 해당 사용자가 소유하고 있는 모든 테이블에 대한 정보를 가지고 있습니다. ALL_ 뷰가 갖는 정보의 부분집합을 제공합니다.

접두어 ALL
접두어 ALL을 가진 뷰들은 모든 사용자가 접근할 수 있으며 보통 OWNER 컬럼을 포함합니다.소유하고 있는 오브젝트에 대한 정보와 사용자에게 공용, 또는 명시적으로 부여된 권한과 롤을 가지고 접근할 수 있는 오브젝트에 대한 정보를 제공합니다.

접두어 DBA
접두어 DBA를 갖는 뷰는 데이터베이스의 모든 오브젝트에 대한 정보를 제공하며 보통 OWNER 컬럼을 포함합니다. 데이터베이스 관리자나 SELECT ANY TABLE 시스템 권한을 부여받은 사용자만이 질의할 수 있습니다.(“권한 관리” 장을 참조하십시오.) 동의어가 정의되어 SELECT ANY TABLE 권한을 가진 모든 사용자가 질의할 수 있습니다. 

 



   데이터 딕셔너리 뷰, 그 컬럼, 그리고 동적 성능 뷰에 대한 개요를 알고  싶으면 DICTIONARY 뷰나 DICT_COLUMNS 뷰를 질의하면 됩니다.
 
       SVRMGR>SELECT *
             2> FROM dictionary
             3> WHERE table_name LIKE ‘%TABLE%’;

        TABLE_NAME                              COMMENTS
        ------------------------        ----------------------------
        ALL_ALL_TABLES                       Description of all object and                                                           relational tables accessible to                                                           the user
        ALL_NESTED_TABLES                Description of nexted tables in                                                           tables accessible to the user
        ALL_OBJECT_TABLES                Description of all object tables                                                           accessible to the user
        ALL_PART_TABLES
        ALL_TABLES                              Description of relational tables                                                           accessible to the user            
        ALL_UPDATABLE_COLUMNS      Description of all updatable                                                           columns
        DBA_ALL_TABLES                      Description of all object and                                                           relational tables in the database
        DBA_NESTED_TABLES               Description of nexted tables                                                           contained in all tables
        DBA_OBJECT_TABLES                Description of all object tables in                                                           the database  

주
데이터 딕셔너리 뷰 DICTIONARY의 동의어 DICT가 있습니다.

        SVRMGR>SELECT column_name, commnets
                    2> FROM dict_columns
                    3> WHERE table_name=‘DBA_TABLES’;

        COLUMN_NAME               COMMNENTS
        -------------------
        OWNER                            Owner of the table
        TABLE_NAME                   Name of th table
        TABLESPACE_NAME        Name of the tablespace containing                                                 the table
        CLUSTER_NAME               Name of the cluster, if any, to which the                                                 table belongs
        IOT_NAME                        Name of th index-only tabl, if any, to                                                 which the overflow  entry belongs

주
뷰와 그 컬럼에 대한 자세한 설명은 Oracle8 Server 참조 매뉴얼을 참조하십시오
 

 데이터베이스를 생성한 후 사용자 SYS로 catalog.sql과 catproc.sql 스크립트를 실행해야만 합니다. UNIX의 경우 $ORACLE_HOME/rdbms/admin 디렉토리에 있으며, NT의 경우 %ORACLE_HOME%\rdbms80\admin 디렉토리에 있습니다. 

catproc.sql 스트립트
catproc.sql 스크립트는 PL/SQL 기능을 사용할 수 있도록 설정하며 RDBMS 기능을 확장할 때 사용되는 몇 개의 PL/SQL 패키지를 생성합니다. 또, 고급 대기 행렬 처리 옵션(advanced queuing option), 테이블스페이스 특정 시간 복구(tablespace point-in-time recovery), 그리고 LOB의 사용을 위한 추가 뷰도 생성합니다.

주
다음 절에서 다루어지듯이 추가 스크립트를 사용하여 오라클 확장 기능을 지원할 수도 있습니다. 

 


UNIX의 $ORACLE_HOME/rdbms/admin 디렉토리(NT의 경우는 %ORACLE_HOME%\rdbms80\admin 디렉토리)에는 관리용 스크립트들이 들어 있으며 네 범주의 파일들로 나누어 볼 수 있습니다:
   utl*.sql
   cat*.sql
   dbms*.sql
   prvt*.plb

utl*.sql 스크립트
데이터베이스가 추가 뷰와 테이블을 필요로 한다면 utl*.sql 스크립트를 꼭 실행해야 합니다.
예를 들어 utlsampl.sql 스크립트는 SCOTT 사용자 소유의 EMP, DEPT, SALGRADE, 그리고 BONUS라는 예제 테이블을 생성해 줍니다.

cat*.sql 스크립트
cat*.sql 스크립트는 데이터 딕셔너리 뷰를 생성합니다.
catalog.sql과 catproc.sql 스크립트 외에도 오라클 유틸리티를 위한 정보를 생성해 주는 스크립트들이 있습니다. 예를 들어 cartrman.sql 스크립트는 Recovery Manager를 위한 복구 카탈로그 테이블과 뷰를 생성합니다. 반면 catnormn.sql은 이러한 테이블과 뷰를 삭제합니다.

dbms*.sql과 prvt*.plb 스크립트
dbms*.sql과 prvt*.plb 스크립트는 정의된 오라클 패키지에 대한 정보를 가지고 있습니다.               (다음장을 참조하십시오.) 이들 패키지는 저장 데이터 베이스 프로시저(stored database procedures)와 함수를 제공하여 오라클 기능을 확장시켜주며 데이터 베이스 관리작업을 간단하게 해줍니다.
대부분의 SQL 스크립트는 catproc.sql 스크립트가 수행되는 동안 실행됩니다. 일부 추가 스크립트는 데이터베이스 관리자가 실행해야만 합니다. 예를 들어 dbmspool.sql 스크립트는 공유 풀(shared pool)의 단편화를 줄이기 위해 공유 풀의 오브젝트 크기를 나타내고 오브젝트의 유지 여부를 표시하도록 만듭니다.

주
대부분의 스크립트들은  사용자 SYS 로 수행해야 합니다. 데이터베이스 관리자는 스크립트를 검사하여 어느 사용자 계정을 사용하여 해당 스크립트를 실행해야 하는지 결정해야 합니다. 

내장 프로그램 단위

Oracle 서버에서 사용자는 내장 프로그램 단위라고 하는 프로시저 스키마 객체를 사용하여 데이터베이스 정보를 액세스하고 처리할 수 있습니다. 내장 프로그램 단위는 특정 작업을 수행하는 SQL 및 프로그램 언어 명령문의 논리적 관련 집합으로 SQL 및 PL/SQL에서 호출할 수 있습니다.

PL/SQL 프로그램 단위

PL/SQL은 SQL의 확장한 Oracle 절차 언어로 PL/SQL를 사용하면 SQL 문을 프로시저 생성자와 혼합할 수 있습니다.

Java 프로그램 단위

데이터베이스 관리자는 Oracle Server의 JServer 구성 요소를 설치하여 Java 프로그램 단위를 실행합니다. SQL 또는 PL/SQL에서 Java 메소드를 호출하려면 사용자는 호출 지정을 기록하여 이 메소드를 등록합니다. 호출 지정은 Java 메소드 이름, 매개변수 유형 및 반환 유형을 해당 SQL 대응 부분에 매핑합니다.

C 프로그램 단위

외부 프로시저는 공유 라이브러리에 저장되는 C 프로그램으로 PL/SQL 프로그램에서 호출할 수 있습니다. 외부 프로시저를 사용하여 데이터베이스 관리자는 Oracle 서버의 기능을 확장할 수 있습니다. 외부 프로시저는 Oracle 서버의 기능과 분리된 주소 영역에서 실행되며 이 외부 프로시저를 호출하려면 프로시저의 이름, 매개변수 유형 및 반환 유형을 등록해야 합니다.

장점

내장 프로그램 단위는 다음 장점을 제공합니다.

참고: 이부분에서는 내장 프로시저에 대한 개요를 설명하여 사용자가 내장 단위를 관리할 수 있도록 합니다. 내장 프로시저, 패키지 및 트리거의 개발과 유지 관리에 대한 자세한 정보는 PL/SQL 프로그램 단위 과정에서 설명합니다.

 

저장 프로시저는 스키마 오브젝트로서 데이터 딕셔너리에 생성되고 저장되는 프로시저, 또는 함수로 SQL과 PL/SQL 명령어들로 구성되어 있습니다. 일단 생성되어 컴파일되면 재컴파일하지 않고 실행할 수 있는 이름을 갖는 오브젝트입니다. 게다가 각 저장 프로시저의 유효성을 검증할 수 있도록 종속성(dependency) 정보가 데이터 딕셔너리에 저장됩니다.
SQL 수식에서 저장 함수를 UPPER와 SUBSTR같은 오라클 내장 함수와 같은 방법으로 사용할 수 있습니다.

프로시저와 함수는 입력 전용(IN), 출력 전용(OUT), 또는 입출력 모두(IN OUT) 될 수 있는 파라미터를 제공합니다. 기본값은 IN 모드입니다


패키지는 보통 데이터베이스에 별도로 저장되는 명세부와 몸체부로 이루어집니다.

- 명세부는 응용 프로그램과의 인터페이스로써 사용 가능한 유형,    변수, 상수, 예외, 커서, 그리고 서브프로그램을 선언합니다.
- 몸체부는 명세부에서 선언한 것을 구현합니다.

패키지의 기능은 저장 프로시저와 유사합니다. 일단 쓰여져서 컴파일되면 그 내용은 많은 응용 프로그램에서 공유할 수 있습니다. 주된 장점 한 가지는 패키지를 구성하는 것중 하나를 처음 호출할 때, 패키지 전체가 메모리로 로드된다는 것입니다

예를 들어 DBMS_SESSION 패키지 명세부는 dbmsutil.sql에 위치하며 패키지 몸체부는 prvtutil.plb 스크립트에 위치합니다.
prvtutil.plb 스크립트(PL/SQL binary를 뜻하는, plb가 기본 확장자입니다.)의 패키지 몸체부는 Wrapper 오라클 유틸리티로 생성되었습니다.
PL/SQL Wrapper는 PL/SQL 소스 코드를 기계어로 변환하여 응용 프로그램 내부를 숨깁니다.

   1 추가 기능이 아니기 때문에 DBMS_SESSION 데이터베이스      패키지를 생성하기 위해 두 스크립트를 실행할 필요는 없습니다.
   2 패키지 프로시저 SET_ROLE을 SQL*Plus나 Server Manager      에서 롤 이름을 IN 파라미터 값으로 하여 실행하십시오.
 
    SVRMGRL> execute DBMS_SESSION.SET_ROLE(‘APP1’);
    Statement processed.

     이로써 SET ROLE 명령을 만들어 내며 “SET ROLE”에 텍스트     APP1을 추가해 주고 SQL 명령으로 수행합니다. 롤을 enable     또는 disable하는 명령으로 “롤 관리” 장에서 자세히 다루어질     것입니다.


슬라이드의 패키지는 오라클이 제공하는 패키지의 일례입니다.
DBMS_SPACE, DBMS_UTILITY, DBMS_ROWID, DBMS_SESSION, 그리고 DBMS_LOB은 catproc.sql 스크립트가 수행될 때 생성됩니다.
DBMS_SPACE, DBMS_UTILITY, DBMS_ROWID, 그리고 DBMS_SESSION은 dbmsutil.sql과 prvtutil.plb 스크립트에 의해 정의됩니다. 반면, DBMS_LOB은 dbmslob.sql과 prvtlob.plb에 의해 정의됩니다.
또 다른 유틸리티, DBMS_SHARED_POOL 패키지는 오브젝트를 공유 풀(shared pool)에 유지하여 보통의 LRU 알고리즘에 의해 없어지지 않도록 합니다. Dbmspool.sql 스크립트를 실행할 때 생성됩니다.

 패키지 프로시저의 예

패키지

 패키지 프로시저

 DBMS_SESSION

 SET_ROLE
 SET_SQL_TRACE
 SET_NLS

 DBMS_UTILITY

 ANALYZE_SCHEMA
 COMPLIE_SCHEMA
 DB VERSION

 DBMS_ROWID

 ROWID_INFO

 DBMS_SPACE

 UNUSED_SPACE
 FREE BLOCKS

 DBMS_SHARED_POOL

 KEEP
 UNKEEP

주
오라클이 제공하는 패키지의 대부분은 ‘Oracle8 Server Application Developer’s Guide’와 ‘Oracle8 PL/SQL User’s Guide and Reference’에서 상세히 다루어지고 있습니다. 


종속성(dependencies)
오라클은 오브젝트 간의 종속성을 데이터 딕셔너리에 자동적으로 기록합니다. 예를 들어 프로시저는 뷰에 종속적일 수 있고, 뷰는 다시 테이블에 종속적일 수 있습니다. 뷰가 정의된 테이블이 삭제되면 오라클은 종속적 뷰와 프로시저에 대한 데이터 딕셔너리 뷰, DBA_OBJECTS의 STATUS 컬럼을 INVALID로 설정합니다.
데이터베이스의 모든 스키마 오브젝트는 VALID나 INVALID 상태를 갖습니다.
   - VALID: 오브젝트가 컴파일되어 있어 참조될 때 즉시 사용될 수       있습니다.
   - INVALID: 오브젝트는 컴파일되어야만 사용될 수 있습니다. 프로     시저, 함수, 패키지의 경우 해당 오브젝트가 컴파일되어야만 하는     뜻이고, 뷰의 경우라면 뷰가 재구문 분석(reparsed)되어야 한다는     의미입니다. 재컴파일한 후에도 오브젝트들이 invalid하다면 구문    
    (syntax) 에러가 발생했을 수도 있습니다.

데이터베이스 오브젝트의 소유자, 이름, 유형, 상태에 대한 정보를 얻으려면 데이터 딕셔너리 뷰 DBA_OBJECTS를 질의하십시오.
        SVRMGR> SELECT object_name, object_type, status
             2> FROM dba_objects WHERE object_name like               ‘DBMS_%’

       OBJECT_NAME                OBJECT_TYPE              STATUS
    ---------------------     ---------------          ----------     DBMS_ALERT                     PACKAGE                     VALID
    DBMS_ALERT                     PACKAGE BODY           VALID
    DBMS_ALERT_INFO            TABLE                          VALID
    DBMS_APPLICATION_INF    PACKAGE                     VALID
    DBMS_APPLICATION_INF    PACKAGE BODY           VALID
    DBMS_AQ                           PACKAGE                     VALID
    DBMS_AQ                           PACKAGE BODY           VALID
        . . .

사용된 파라미터에 대한 정보를 얻으려면 Server Manager의 DESCRIBE 명령을 실행하십시오.
        SVRMGR> DESCRIBE dbms_session.set_rol
        procedure SET_ROLE (ROLE_CMD VARCHAR2);

주
프로시저나 함수 이름 없이 패키지 이름만으로 Server Manager 유틸리티의 DESCRIBE 명령을 실행하면 DBMS_SESSION 패키지의 모든 프로시저와 함수를 보여줍니다.

        svrmgr> describe dbms_session
        package dbms_session is
        -----------
        -- OVERVIEW
        -- This package provides acces to SQL “alter session”
        -- statements, and other session information from, stored
        -- procedures.
        ---------------------------
        -- PROCEDURES AND FUNCTIONS
             procedure set_role(role_cmd varchar2);
        -- Equivalent to SQL “SET ROLE . . .”.
        -- Input arguments:
        --    role_cmd
        -- This text is appended to “set role” and then executed              as
        -- SQL.
        procedure set_sql_trace(sql_trace boolean);
        -- Equivalent to SQL “ALTER SESSION SET SQL_TRACE . ."
        -- Input arguments;
        --    sql_trace
        --        TRUE of FALSE.   Turns tracing on or off.
        procedure set_nls(param varchar2, value varchar2); 

 


ALTER TABLE ADD, RENAME, DROP, 그리고 CREATE OR REPLACE 등의 DDL 명령을 수행하고 나면 종속적 오브젝트의 상태가 INVALID로 바뀝니다.

Import 유틸리티는 참조 오브젝트를 생성한 후에는 종속적 오브젝트를 생성할 수 없을 수 있기 때문에 Import 유틸리티로 종속적 뷰와 종속적 저장 프로시저를 로드하면 오브젝트 상태가 INVALID가 될 수도 있습니다.

오라클 서버는 invalid한 뷰나 PL/SQL 프로그램을 다음 번 사용할 때 재 컴파일합니다. 또, 사용자가 적절한 SQL 명령을 써서 오라클 서버로 하여금 뷰나 저장 프로시저, 또는 패키지를 재 컴파일하도록 할 수도 있습니다.

주
   - Import 유틸리티 사용법은 “데이터 로드와 재구성” 장에서      자세히 다루어집니다.
   - 저장 프로시저와 패키지 재컴파일은 PL/SQL Program Units      과정에서 다루어집니다

개요

트리거는 PL/SQL, Java 또는 C로 작성한 프로시저로 테이블 수정, 객체 생성, 일부 사용자 작업 또는 데이터베이스 시스템 작업 발생 시 암시적으로 실행됩니다. 이러한 프로시저는 PL/SQL 또는 Java로 작성하여 데이터베이스에 저장되거나 외부 프로시저로 작성될 수 있습니다.

트리거는 내장 프로시저와 비슷하지만 프로시저와 트리거는 호출 방식이 다릅니다. 프로시저는 사용자, 응용 프로그램 또는 트리거에 의해 명시적으로 실행되며 트리거(한 개 또는 그 이상)는 트리거 이벤트가 발생할 때 연결된 사용자나 사용 중인 응용 프로그램에 상관없이 Oracle 서버에서 암시적으로 실행됩니다.

트리거 사용 방법

트리거는 Oracle 서버의 표준 성능을 보완하여 높은 수준의 사용자 정의 데이터베이스 관리 시스템을 제공할 수 있습니다. 예를 들어, 트리거를 사용하여 다음을 수행할 수 있습니다.

 

트리거의 세 부분

트리거는 다음 세 가지 기본적인 부분으로 구성됩니다.

트리거 이벤트 또는 트리거 문

트리거 이벤트 또는 트리거 문은 트리거를 시작하는 SQL 명령문, 데이터베이스 이벤트 또는 사용자 이벤트입니다. 다음 트리거 이벤트는 대부분 데이터베이스 관리자가 사용합니다.

또한 응용 프로그램 개발자는 특정 테이블 또는 뷰에 대해 INSERT, UPDATE 또는 DELETE 문이 실행될 때 시작하는 트리거를 작성할 수도 있습니다.

트리거 제한 사항

트리거 제한 사항은 트리거를 실행하기 위해 TRUE여야 하는 부울(논리적) 표현식을 지정합니다. 트리거 제한 사항이 FALSE 또는 UNKNOWN인 경우 트리거 작업은 실행되지 않으며 트리거 제한 사항은 WHEN 절에 코드화됩니다.

트리거 작업

트리거 작업은 트리거 문이 실행되고 트리거 제한 사항이 TRUE일 경우에 실행되는 SQL 문 및 코드를 포함하는 프로시저(PL/SQL 블록, Java 프로그램 또는 C 콜아웃)입니다. 내장 프로시저와 마찬가지로 트리거 작업은 SQL 및 PL/SQL 또는 Java 문을 포함할 수 있고 PL/SQL 언어 생성자(변수, 상수, 커서, 예외 사항 등) 또는 Java 언어 생성자를 정의할 수 있으며 내장 프로시저를 호출할 수 있습니다.

 

LOGON 트리거

슬라이드의 예제는 CREATE OR REPLACE TRIGGER 명령으로 생성 또는 대체되는 트리거를 보여줍니다. 트리거는 다음 특성을 가집니다.

트리거 정보는 DBA_TRIGGERS 데이터 딕셔너리 뷰에 저장됩니다.

트리거 구현에 대한 자세한 내용은 PL/SQL 내장 프로그램 단위 과정에서 설명합니다.

 

 요약참조

 관련내용

 참조

 초기화 파라미터

 없슴

 동적 성능 뷰

 없슴

 데이터 딕셔너리 뷰

 DICTIONARY
 DICI
 DICT_COLUMNS
 DBA_OBJECTS

 명령어

 없슴

 패키지 프로시저와 함수

 DBMS_SESSIONNET_ROLE