[DB] 데이터사전(data dictionary) - oracle, mysql / information_schema

[oracle]

데이터 사전?

: 오라클 데이터베이스 테이블은 사용자 테이블과 데이터 사전으로 나뉩니다.

 

사용자테이블: 데이터베이스를 통 해 관리할 데이터를 저장하는 테이블

데이터사전:  데이터베이스를 구성하고 운영하는데 필요한 모든 정보를 저장한 특스 테이블, 데이터베이스가 생성되는 시점에 자동으로 만들어집니다.

 

 

데이터사전에는 데이터베이스 메모리,성능,사용자,권한,객체 등 데이터베이스 운영에 중요한 데이터가 보관되어있어, 이 데이터에 문제가 발생한다면 오라클  데이터베이스 사용이 불가능 해질 수도있습니다.

따라서 데이터 사전정보에 직접 접근하거나 작업하는 것을 허용하지 않습니다.

 

 

오라클 데이터 사전 뷰
접두어	설명
USER_XXXX	현재 데이터베이스에 접속한 사용자가 소유한 객체정보
ALL_XXXX	현재 데이터베이스에 접속한 사용자가 소유한 객체 또는 다른 사용자가 소유한 객체 중 사용허가를 받은객체, 즉 사용가능한 모든 객체 정보
DBA_XXXX	데이터베이스 관리를 위한 정보 (데이터베이스 관리 권한을 가진 SYSTEM, SYS 사용자만 열람가능)
V$_XXXX	데이터베이스 성능 관련 정보(X$_XXXX 테이블의 뷰)

 

데이터 사전 EX
오라클에서 사용 가능한 데이터 사전 조회하기	SELECT * FROM  DICT;
SCOTT 계정이 가지고있는 객체 정보 조회	SELECT TABLE_NAME FROM USER_TABLES;
SCOTT 계정이 사용할 수있는 객체 정보	SELECT OWNER, TABLE_NAME FROM ALL_TABLES;
SCOTT 계정으로 DBA 접두어 사용하기	SELECT * FROM DBA_TABLES;

 

---

[mysql]

 

MySQL 주요 Data Dictionary

1. information_schema

2. mysql

3. sys

4. performance_schema 

 

information_schema ?

: mysql 서버 내에 존재하는 DB의 메타정보(테이블 , 컬럼, 인덱스 등의 스키마 정보)를 모아둔 db, 읽기전용, 단순조회만 가능

 

 

information schema 테이블 종류 조회하기

select TABLE_SCHEMA, TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = 'information_schema';

 

information schema 주요 테이블

Tables_in_information_schema
COLUMNS	모든 스키마의 컬럼 확인
ENGINES	사용되는 엔진 확인
EVENTS	생성된 Event(스케줄) 확인
KEY_COLUMN_USAGE	사용된 키 컬럼 확인
PROCESSLIST	수행 중인 프로세스 리스트
SCHEMATA	생성된 스키마 확인
SCHEMA_PRIVILEGES	스키마 권한 확인
TABLES	생성된 모든 테이블 정보
TABLE_PRIVILEGES	테이블 권한 확인
TRIGGERS	생성된 트리거 확인 가능
USER_PRIVILEGES	사용자 권한 정보
VIEWS	생성된 뷰 정보

사용 예제

information_schema EX
테이블 이름 조회하기	SELECT table_name FROM information_schema.tables;
컬럼 조회하기	1 2 SELECT table_name, COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA="SCOTT"; Colored by Color Scripter cs
PK 로 선언된 컬럼 조회하기	1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT      TABLE_NAME,      COLUMN_NAME,      IS_NULLABLE,      DATA_TYPE,      COLUMN_TYPE,      COLUMN_KEY,      EXTRA FROM    information_schema.COLUMNS col WHERE   col.table_schema = 'SCOTT'   and column_key = 'PRI'    and extra = 'auto_increment'; cs
PK로 잡혀있는 INT타입의 컬럼 AI값 확인하기	1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT   col.TABLE_NAME,   col.COLUMN_NAME,   col.COLUMN_TYPE,   tab.AUTO_INCREMENT FROM    information_schema.TABLES tab,    information_schema.COLUMNS col WHERE   col.TABLE_NAME = tab.TABLE_NAME   and col.table_schema = 'SCOTT'   and col.extra = 'auto_increment'   and col.column_key = 'PRI'   and col.COLUMN_TYPE = 'int';  cs

 

mysql information_schema 참고 :

https://dev.mysql.com/doc/mysql-infoschema-excerpt/8.0/en/information-schema.html

 

 

MySQL :: MySQL Information Schema :: 1 INFORMATION_SCHEMA Tables

 

mysql 

 

TABLE_SCHEMA	TABLE_NAME
mysql	columns_priv
mysql	column_stats
mysql	db
mysql	event
mysql	func
mysql	general_log
mysql	gtid_slave_pos
mysql	help_category
mysql	help_keyword
mysql	help_relation
mysql	help_topic
mysql	host
mysql	index_stats
mysql	innodb_index_stats
mysql	innodb_table_stats
mysql	plugin
mysql	proc
mysql	procs_priv
mysql	proxies_priv
mysql	roles_mapping
mysql	servers
mysql	slow_log
mysql	tables_priv
mysql	table_stats
mysql	time_zone
mysql	time_zone_leap_second
mysql	time_zone_name
mysql	time_zone_transition
mysql	time_zone_transition_type
mysql	transaction_registry
mysql	user

 

MY SQL 내부에 생성된 USER정보	select * from mysql.user;
mysql 에서 db로그를 table타입으로 저장할 때 로그가 쌓이는 테이블 조회	select * from mysql.general_log;