240221 DB - Data Dictionary, SQL 활용

Data Dictionary

구성요소

1. Base tables

- 오라클 본인의 수첩이라고 생각하자

- DB의 모든 정보를 DBMS가 보기 쉬운 형태로 기록해 놓은 테이블

- 사용자는 권한이 없음

 

2. Data Dictionary views

- Base table을 사용자가 보기 쉬운 형태로 기록해 놓은 테이블

- 읽기 전용(select만 가능함)

- 종류

 1) user_xxx : 내(접속 유저)가 소유한 object 정보 조회(user_constraints 중요함)

-- user_tables(내 소유의 테이블 정보)

desc user_tables;

select table_name, tablespace_name from user_tables;

 

그림1.

 

 

-- user_constraints(테이블의 제약조건)
-- constraint_name, constraint_type, table_name, search_condition, r_constraint_name 컬럼을 자주 사용

desc user_constraints;

select constraint_name, constraint_type, search_condition, r_constraint_name
from user_constraints -- 내 소유의 테이블에 선언된 제약조건 정보
where lower(table_name) = 'employees';

 

그림2.

P : PK, U : UK, R : FK(참조하고 있는 테이블의 제약조건명이 나타남), C : CK,NN

constraint_type은 CK나 NN을 C로 나타내기 때문에 search_condition을 함께 사용해야함

 

-- 내 정보 조회

desc user_users;

select username, user_id, default_tablespace, created
from user_users;

 

그림 3.

추가적으로 user_views, user_indexes,.. 등이 있음.

 

 2) all_xxx : 내가 소유하거나 (권한을 받은)접근 가능한 object 정보를 조회

desc all_tables;

select owner, table_name
from all_tables;

 

그림 4.

추가적으로 all_views, all_indexes 등이 있음

 

 3) dba_xxx : DB의 모든 정보를 조회, DBA만 조회 가능. 실시간으로 반영되지 않음

현재 hr로 접속해있고 그림 5와 같이 명령을 내리면 불가능하다는 것을 알 수 있음.

그림 5.

dba_tables, dba_users(DB에 존재하는 user 리스트), dba_data_files 등이 있음

 

 4) v$xxx : DB의 모든 정보 조회, DBA만 조회 가능. 실시간으로 반영됨

v$datafile, v$session(DB에 접속된 user리스트) 등이 있음

 

※ v$datafile, dba_data_files 모두 DB에 존재하는 datafile 리스트

※ dba_xxx, v$xxx => DB관리자가 사용하기 때문에 DB관리자가 아니라면 이런 것이 있다 정도로만 이해하자

 

데이터 검색을 위한 주요 DATA DICTIONARY VIEWS

dict(ionary) : 모든 data dictionary view 리스트를 조회

desc dict;
select * from dict where lower(table_name) = 'user_tables';
select * from dict where lower(table_name) = 'user_users';

 

그림 6

 

user_obj(ect) : 사용자 소유의 모든 오브젝트(뷰, 인덱스, 테이블 등)에 대한 정보를 조회

 

user_tab_columns(cols) : 사용자 소유의 모든 테이블을 구성하고 있는 컬럼에 대한 정보를 조회

※user_tables : 내 소유의 테이블 정보를 조회

desc user_tab_cols;
select column_name, data_type, data_length, data_default 
from user_tab_cols
where lower(table_name) = 'employees';

 

그림 7.

추가적인 자세한 정보를 알기 위해서 사용

---

SQL 활용

데이터정의어(DDL) - table

테이블 생성(create talbe)

테이블명, 컬럼명, 데이터타입, 컬럼 사이즈를 반드시 지정해야하며, default값, 제약조건은 옵션

create table 테이블명
(컬럼명1 데이터타입1(컬럼사이즈1),
컬럼명2 데이터타입2(컬럼사이즈2),...);

 

테이블명, 컬럼명 선언 시 이름 지정 규칙(DB안에서 모두 해당함)

1. 문자로 시작해야함
2. 문자,숫자, 특수문자(_,#,$) 포함 가능
3. 최대 30bytes까지 지정 가능함
4. DB 예약어 사용불가
5. 동일한 유저가 동일한 이름의 object 가질 수 없음 

 

데이터 타입

• 문자 : varchar2(가변 길이 데이터 타입, 공간효율이 좋음), char(고정 길이 데이터 타입, 성능이 좋)
  ※Mysql에서는 varchar
• 숫자 : number(p) - 정수형, number(p,s) - 실수형 / p 전체자리수, s 소수점 이하 자리수
  ex) number(3) => 999, number(5,2) => 999.99
• 날짜 : date : 년/월/일, timestamp : 년/월/일/시/분/초
  ※날짜는 사이즈를 지정하지 않음

※

-- Mysql로 작성
create table test1
(id int, name varchar(10)); 

-- Oracle로 작성
create table test2
(id number, name varchar2(20));

 

---

실습

-- 테이블 생성
create table dept
(deptno number(2),
dname varcahr2(14),
loc varchar2(13),
create_date date default sysdate);

-- 구조 확인
desc dept;

-- 데이터 삽입
insert into dept
values(10, 'AAA', 'A100', '24/01/01');
-- ※ 한글버전같은 경우 rr/mm/dd

-- 확인
select * from dept;

-- 데이터 삽입
insert into dept(deptno, dname)
values(20, 'BBB');

-- 데이터 삽입(명시적으로 default값 삽입)
insert into dept
values(30, 'CCC', 'C100', default);

-- 저장
commit;