시스템 카탈로그와 데이터 사전

시스템 카탈로그(system catalog)는 데이터베이스가 자기 안에 무엇이 있는지를 적어 둔 표의 모음입니다. 표·열·제약 조건·인덱스를 설명하는 데이터가 들어 있고, DBMS가 저절로 고치며 사용자는 조회만 합니다.

SELECT * FROM 학생을 실행하면 DBMS는 학생 표에 어떤 열이 몇 개 있는지부터 알아야 합니다. 학과코드에 외래키가 걸려 있는지, 이름 열에 인덱스가 있는지도 알아야 합니다. 이런 정보는 학생 표 안에 없습니다. 학생 표에는 학생의 행만 있습니다. 그래서 DBMS는 '표를 설명하는 표'를 따로 두고 거기에 적어 둡니다. 사용자도 그 표를 조회해서 내가 만든 표의 열 이름과 제약 조건 이름을 확인할 수 있습니다. 필기는 시스템 카탈로그가 무엇을 담고 누가 고치는지를 묻고, 실기는 이름을 쓰게 합니다.

SELECT TABLE_NAMEFROM USER_TABLESORDER BY TABLE_NAME;

CREATE TABLE 강의실 (  호수 NUMBER PRIMARY KEY,  건물 VARCHAR2(10 CHAR));

ALTER TABLE 과목  RENAME TO 교과목;

UPDATE USER_TABLESSET TABLE_NAME = '과목2'WHERE TABLE_NAME = '교과목';

SELECT COLUMN_NAME,  DATA_TYPEFROM USER_TAB_COLUMNSWHERE TABLE_NAME = '학생';

SELECT TABLE_NAMEFROM USER_TABLESORDER BY TABLE_NAME;

1표를 설명하는 표

그림은 표를 만들고 이름을 바꾸는 동안 이 표가 어떻게 따라 바뀌는지 보입니다. 데이터베이스에는 내가 만든 표를 설명하는 표가 따로 있습니다. Oracle에서 내 표 목록을 담은 것은 USER_TABLES입니다. 조회하면 과목·수강·학과·학생 네 행이 나옵니다. 학생의 이름이나 점수 같은 행의 값은 들어 있지 않습니다. CREATE TABLE로 강의실 표를 만들면 USER_TABLES에 강의실 행이 저절로 하나 더해집니다. 사용자는 INSERT를 한 적이 없습니다. DDL 문을 실행할 때마다 DBMS가 이 표를 고칩니다. 표 이름을 바꾸면 USER_TABLES의 칸도 저절로 바뀝니다. 시스템 카탈로그는 언제나 지금 상태를 담고 있습니다. 표 이름, 열, 제약 조건, 인덱스는 각각 다른 뷰에 담깁니다. 찾는 것에 맞는 뷰를 고릅니다.

2사용자는 조회만 할 수 있다

같은 칸을 사용자가 UPDATE로 직접 고치려 하면 막힙니다. 사용자에게는 조회할 권한만 있습니다. 고치는 길은 DDL 문뿐입니다. 사용자가 CREATE·ALTER·DROP으로 객체를 바꾸면 DBMS가 그에 맞춰 이 표를 고칩니다.

3메타데이터와 데이터 사전

표를 설명하는 데이터를 메타데이터(metadata)라고 합니다. '데이터에 대한 데이터'라는 뜻입니다. 학생 표에서 '101·김민수·3·CS'는 데이터이고, '학생 표에는 열이 넷 있고 학번은 숫자이며 기본키다'는 메타데이터입니다. 시스템 카탈로그는 메타데이터를 담은 표의 모음이고, 자료에 따라 데이터 사전(data dictionary)이라고도 부릅니다. 시험은 두 이름을 모두 씁니다. Oracle이 쓰는 이름은 데이터 사전입니다.

4뷰 이름의 앞머리와 동적 성능 뷰

사용자가 보는 것은 그 표를 그대로 보여 주는 뷰입니다. 뷰 이름의 앞머리가 누구의 것을 보여 주는지를 가립니다.

• 세 앞머리에 같은 이름이 붙어 USER_TABLES·ALL_TABLES·DBA_TABLES처럼 짝을 이룹니다.
• 예제 사용자로 ALL_TABLES에서 내가 주인인 표를 세면 4가 나옵니다. 내가 가진 표도 ALL_ 뷰에 들어 있습니다. DBA_TABLES는 관리자 권한이 없어 ORA-00942(없는 표) 오류가 납니다.

지금까지 본 뷰는 DDL을 실행할 때만 바뀌어 정적(static) 데이터 사전 뷰라고 합니다. 이와 달리 지금 접속한 세션이나 메모리 사용량처럼 데이터베이스가 돌아가는 동안 계속 바뀌는 것을 보여 주는 뷰도 있습니다. Oracle에서 이름이 V$로 시작하는 이 뷰를 동적 성능 뷰(dynamic performance view)라고 부릅니다. 동적 성능 뷰도 사용자가 고치거나 지울 수 없어서 고정 뷰(fixed view)라고 합니다.

V$SESSION   접속한 세션
V$SQL       실행된 SQL 문
V$DATABASE  전체 상태

예제

결과 표는 Oracle에서 돌린 결과입니다. 뷰와 열 이름은 Oracle이 정한 그대로 대문자로 씁니다. 내가 만든 표와 열 이름만 한글입니다.

내 표 목록

SELECT TABLE_NAME
FROM USER_TABLES
ORDER BY TABLE_NAME;

이 네 행은 표 이름일 뿐이고, 표 안의 행은 들어 있지 않습니다.

표를 만들면 행이 저절로 더해진다

강의실 표를 하나 만든 뒤 같은 조회를 다시 합니다.

CREATE TABLE 강의실 (
  호수 NUMBER PRIMARY KEY,
  건물 VARCHAR2(10 CHAR)
);
SELECT TABLE_NAME
FROM USER_TABLES
ORDER BY TABLE_NAME;

강의실 행이 더해져 5행입니다. INSERT INTO USER_TABLES⁠를 쓴 적이 없습니다. DDL 문을 실행하면 DBMS가 데이터 사전을 함께 고칩니다. 표를 지우면(DROP TABLE) 그 행도 저절로 사라집니다.

열과 제약 조건

USER_TAB_COLUMNS는 열을, USER_CONSTRAINTS는 제약 조건을 담습니다. COLUMN_ID는 열의 순서, NULLABLE은 NULL을 넣을 수 있는지입니다.

SELECT COLUMN_ID,
  COLUMN_NAME,
  DATA_TYPE,
  NULLABLE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = '학생'
ORDER BY COLUMN_ID;

학과코드만 NULLABLE이 Y입니다. 학생 107 강도현의 학과코드가 NULL일 수 있는 까닭입니다.

제약 조건은 CONSTRAINT_TYPE 한 글자로 종류를 보입니다. P는 기본키, R은 외래키, C는 CHECK와 NOT NULL, U는 UNIQUE입니다.

SELECT CONSTRAINT_NAME,
  CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = '학생'
  AND CONSTRAINT_NAME
    NOT LIKE 'SYS%'
ORDER BY CONSTRAINT_NAME;

• 세 제약 조건은 표를 만들 때 이름을 붙여 둔 것입니다. 그래서 어긋났을 때 나는 오류 문구에도 이 이름이 그대로 나옵니다.
• NOT LIKE 'SYS%'로 거른 줄이 둘 더 있습니다. 이름을 붙이지 않은 NOT NULL 제약 조건이라 DBMS가 SYS_로 시작하는 이름을 붙였고, 그 번호는 표를 만들 때마다 달라집니다.
• 인덱스는 USER_INDEXES에 담깁니다. 앞 레슨에서 기본키 인덱스가 저절로 생기는 것을 이 뷰로 확인했습니다.

직접 고칠 수는 없다

표 이름을 바꾸는 일을 두 가지 방법으로 해 봅니다. 먼저 데이터 사전 뷰를 UPDATE로 직접 고쳐 봅니다.

UPDATE USER_TABLES
SET TABLE_NAME = '과목2'
WHERE TABLE_NAME = '과목';

ORA-02030(can only select from fixed tables/views) 오류가 납니다. 사용자에게는 조회할 권한만 있습니다. 문구의 fixed는 사용자가 고치거나 지울 수 없는 뷰라는 뜻입니다.

같은 일을 DDL 문으로 하면 됩니다. 표 이름을 바꾸면 데이터 사전의 칸은 DBMS가 알아서 고칩니다.

ALTER TABLE 과목
  RENAME TO 교과목;
SELECT TABLE_NAME
FROM USER_TABLES
ORDER BY TABLE_NAME;

과목 칸이 교과목으로 바뀌었습니다. 데이터 사전을 고치는 길은 이것뿐입니다. 사용자는 DDL 문으로 객체를 바꾸고, 데이터 사전은 그 결과를 비춥니다.

시험 포인트

• 필기: 시스템 카탈로그는 표·열·제약 조건·인덱스를 설명하는 데이터, 곧 메타데이터를 담은 표의 모음입니다. 자료에 따라 데이터 사전이라고도 부릅니다. DDL 문을 실행하면 DBMS가 저절로 고치고, 사용자는 조회만 할 수 있습니다. 사용자가 INSERT·UPDATE·DELETE로 직접 고치는 것은 막혀 있습니다. 뷰 이름의 앞머리는 USER_(내 것)·ALL_(권한을 받은 것)·DBA_(모든 것)로 나뉩니다. DDL 때만 바뀌는 것이 정적 데이터 사전 뷰, 돌아가는 동안 계속 바뀌는 것이 동적 성능 뷰(V$)입니다.
• 실기: 이름은 시스템 카탈로그(System Catalog), 데이터 사전(Data Dictionary), 메타데이터(Metadata), 동적 성능 뷰(Dynamic Performance View)로 씁니다. 시스템 카탈로그와 데이터 사전은 같은 것을 가리키는 두 이름이라 어느 쪽을 써도 됩니다.

확인 문제

1. 다음 가운데 메타데이터인 것을 고르세요.

• ① 수강 표에서 학번 105의 자료구조 점수가 100이다
• ② 수강 표의 점수 열이 숫자 형이고 NULL을 넣을 수 있다
• ③ 과목 표에 자료구조·네트워크·운영체제 세 과목이 있다
• ④ 학과 표에서 컴퓨터 학과의 건물이 공학관이다

2. 다음 (가)~(다)에 맞는 이름을 한글 이름과 영문으로 함께 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 수강 표를 지웠더니 사용자가 아무 문장도 더 쓰지 않았는데 표 목록에서 수강 줄이 없어졌습니다. 이렇게 데이터베이스가 자기 안에 무엇이 있는지를 적어 두고 스스로 고치는 표의 모음입니다. 출제기준이 두 이름을 모두 쓰니 둘 다 쓰세요.
• (나) '수강 표에는 열이 셋 있고 학번과 과목코드가 기본키다'처럼, 행에 든 값이 아니라 표 자체를 설명하는 데이터입니다.
• (다) 지금 접속한 세션과 메모리 사용량처럼 데이터베이스가 돌아가는 동안 계속 바뀌는 것을 보여 주는, 이름이 V$로 시작하는 뷰입니다.

3. 과목 표에 학기 NUMBER라는 열을 하나 더했습니다. 그 뒤 과목 표의 열을 열 순서대로 조회하려 합니다. (1) 조회 쿼리를 쓰고 (2) 결과를 표로 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

ALTER TABLE 과목
  ADD 학기 NUMBER;
SELECT COLUMN_ID,
  COLUMN_NAME,
  NULLABLE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = '과목'
ORDER BY COLUMN_ID;