[Db Admin] 5.Data Dictionary Views and Standard Packages

1. Base Tables and Data Dictionary Views
   - Data Dictionary Views
     base table 에 대한 간단한 정보를 갖고 있다.
     catalog.sql file로 create한다.
     
   - Base Tables
     sql.bsq file로 create한다.
     
2. Data Dictionary 의 구성

DBA_xxx    : object of the entire database  ALL_xxx    : object can be accessed by the user  USER_xxx    : objects owned by the user

3. Data Dictionary: Views Examples and Categories
 

Views	Desciption
dictionary dict_columns	General overview
dba_tables dba_objects dba_lobs dba_tab_columns dba_constraints	Information related to the user objects such as tables, constraints, large objects and columns
dba_users dba_sys_privs dba_roles	Information about user privileges and roles

   SVRMGR> SELECT *
        2  FROM dictionary
        3  WHERE table_name LIKE '%TABLE%' ;
   --> TABLE에 관련된 모든 data dictionary 정보를 보여줌.
   
   SVRMGR> SELECT column_name, comments
        2  FROM dict_columns
        3  WHERE table_name='DBA_TABLES' ;
   --> Dictionary에 대한 정보를 보여줌.
   
4. Data Dictionary Views: Examples and Categories
 

Views	Desciption
dba_extents dba_free_space dba_segments	Space allocation for database objects
dba_rollback_segs dba_data_files dba_tablespaces	General database sturctures
dba_audit_trail dba_audit_objects dba_audit_obj_opts	Auditing information

  * Data Dictionary View를 생성하려면...
    $ORACLE_HOME/rdbms/admin 에 있는 caelog.sql, catproc.sql을 실행 시킨다.
    
5. Administrative Scripts
   ; $ORACLE_HOME/rdbms/admin 에 있다.

   cat*.sql : catalog and data dictionary information
   dbms*.sql : database package specifications
   prvt*.plb : wrapped database package code
   utl*.sql : Views and tables for database utilities
   
6. Oracle-Supplied Packages
   
   DBMS_LOB : Provides routines for operations on BLOB and CLOB datatypes
   DBMS_SESSION : Generates SQL commands like ALTER SESSION or SET ROLE
     └ (Package procedures : SET_ROLE, SET_SQL_TRACE, SET_NLS)
   DBMS_UTILITY : Provides various utility routines
     └ (Package procedures : ANALYZE_SCHEMA, COMPILE_SCHEMA, DB_VERSION)
   DBMS_SPACE : Provides segment space availability information
     └ (Package procedures : UNUSED_SPACE, FREE_BLOCKS)
   DBMS_ROWID : Provides ROWID information
     └ (Package procedures : ROWID_INFO)
   DBMS_SHARED_POOL : Keeps and unkeeps information in the shared pool
     └ (Package procedures : KEEP, UNKEEP)

7. Stored Objects에 관한 정보를 갖고 있는 Data Dictionary
   
   DBA_OBJECTS (owner, object_name, object_type, status)
   
   SVRMGR> SELECT object_name, object_type, status
        2  FROM dba_objects
        3  WHERE object_name like 'DBMS_%' ;
        
   * DESCRIBE command
     SVRMGR> DESC dbms_session.set_role procedure SET_ROLE (ROLE_CMD VARCHAR2) ;

---

[ Lab ]

---

1. SQL*Plus에 system/manager로 접속하여, DB User를 확인하십시오.
$ sqlplus system/manager
SQL> select username, created from dba_users ;

2. database의 각 tablespace 별로 사용 가능한 space가 얼마나 있는지 확인하십시오.
SQL> select tablespace_name, sum(blocks), sum(bytes)
  2  from dba_free_space
  3  group by tablespace_name ;
  
3. database 내에 생성되어 있는 standard package 를 확인하십시오.
SQL> select object_name
  2  from dba_objects
  3  where object_type like 'PACK%' ;