Oracle 기초 : 실전(16) Data Dictionary

Introduction of Data Dictionary

https://docs.oracle.com/cd/B10501_01/server.920/a96524/c05dicti.htm

Oracle Docs에서는 Data Dictionary를 "Oracle Database에서 가장 중요한 파트 중 하나인 Data Dictionary는, Database에 관한 정보를 읽기 전용인 Table들의 집합으로 제공하고 있다."고 소개하고 있다. (One of the most important parts of an Oracle database is its data dictionary, which is a read-only set of tables that provides information about the database.) 즉 이는 자원을 효율적으로 관리하기 위하여 DB Object에 대한 메타 정보를 저장 및 관리하는 테이블이다. 사용자가 이 Data Dictionary를 생성/수정/삭제할 일은 없으며, 사용자가 객체를 제어할 때 자동으로 갱신된다.

Table 생성시에 작성한 Nullablity, default Values, Comments, Constraints등의 정보는 일반적인 방법으로는 조회할 수 없고, 이 Data Dictionary의 Table 조회를 통해 가능하다. 사용자가 원하는 정보에 접근하기 위해서 위의 이미지와 같이 아주 다양한 종류의 Table들이 Oracle 내부적으로 존재하고 있으며, 해당 정보에 대응되는 Table을 골라서 평소 Table 정보를 조회할 때와 같이 select Col from TABLE_NAME(여기서는 Data Dictionary에 해당하는 Table) where Condition(add'l)의 양식으로 조회하면 된다.

---

Types of Data Dictionary

NAMING RULE: 아래의 접두사의 후위에 위치하는 단어들은 항상 복수형을 취한다.

1. user_...: 시용자 소유의 객체를 조회한다.
2. all_...: 사용자 소유의 객체 포함, 사용 권한을 부여받은 객체를 조회한다.
3. dba_...: 관리자만 접근 가능하며 모든 Database Object를 조회할 수 있다.

---

이중 가장 사용빈도가 높은 Data Dictionary Table들을 다음과 같이 정리한다:

---

LOOK UP(1): Tables & Privileges

--LOOK UP ALL TABS FROM CURR-USER
select *
from user_tables;

--LOOK UP ALL TABS THAT CURR-USER CAN USE 
select * 
from all_tables;

--LOOK UP ALL TABS THAT SUPER USER CAN USE
select *
from dba_tables

--LOOK UP THE ROLE THAT THE USER HAS
select *
from dba_role_privs
where grantee = 'USERNAME'; --(add'l)

--LOOK UP THE PRIVILEGE THAT THE USER HAS
select *
from dba_sys_privs
where grantee = 'USERNAME';

---

LOOK UP(2): default Val, Datatypes, Constraints, Comments Info in Col.

순서대로 Table 내 각 Column들에 설정한 Constraint(제약조건) 조회, Table 생성 시의 Datatypes, Table 및 Column에 설정한 Comments, 각 Columns에 할당된 default값 존재 여부 및 Default Values 와 Nullability 조회 코드이다.

--LOOK UP THE CONSTRAINTS INFORMATION IN TABLE
select  UC.table_name, UCC.column_name, constraint_name, 
		UC.constraint_type, UC.search_condition
from user_constraints UC join user_cons_columns UCC
using (constraint_name)
where UCC.table_name = 'TABLE_NAME'

--LOOK UP THE DATATYPES IN TABLE
select *
from user_tab_cols
where table_name = 'TABLE_NAME'

--LOOK UP THE TABLE/COL COMMENTS
select *
from user_col_comments UCC join user_tab_comments UTC
using(table_name)
where table_name = 'TABLE_NAME'

--LOOK UP THE DEFAULT DATA/NULLABLITY
select *
from user_tabl_cols
where table_name = 'TABLE_NAME'

---