오라클 테이블 관리와 데이터 딕셔너리(효율적인 데이터베이스 운영)
데이터베이스는 현대사회의 비즈니스에 있어서 핵심적인 자원으로 여겨지는데요, 따라서 데이터베이스의 테이블 관리와 데이터 딕셔너리는 데이터베이스 운영에서 매우 중요한 부분이라고 할 수 있습니다. 이번 글에서는 테이블 생성, 수정, 삭제, 주석 추가와 함께 데이터 딕셔너리의 역할과 활용 방법에 대해 알아보겠습니다.
테이블 생성과 관리
테이블을 생성할 때는 테이블 이름 정의 규칙이 존재하는데요, 테이블과 칼럼(필드) 이름은 문자로 시작하고 30자 이내여야 하며, 문자, 숫자, 특수문자(_,$,#)를 사용할 수 있습니다. 테이블명은 대소문자를 구분하지 않지만, 실제로 데이터 딕셔너리에는 대문자로 저장됩니다. 테이블 생성은 CREATE TABLE 명령문을 사용하며, 기본값 설정도 가능합니다. 자세한 테이블명 규칙을 알아볼까요?
테이블 이름 생성 규칙
- 테이블과 칼럼 이름은 문자(A-Z a-z)로 시작하고 30자 이내로 가능하다.
- 테이블 이름은 문자, 숫자, 특수문자(_, $, #)를 사용할 수 있다.
- 테이블 이름은 대소문자를 구분하지 않지만, 데이터 딕셔너리에는 대문자로 저장된다. 소문자로 저장하고 싶은 경우에는 단일 인용부호(”)를 사용한다.
- 테이블 이름은 동일한 사용자가 소유한 다른 객체의 이름과 중복될 수 없다.
- 서로 다른 테이블에서 동일한 데이터를 저장하는 칼럼 이름은 가능하면 같은 이름을 사용하는 것이 좋다(권장)
테이블 생성 예)
CREATE [ GLOBAL TEMPORARY] TABLE [schema.]table명
(column datatype [DEFAULT expression] [column_constraint clause]
[,…]);
- CREATE TABLE 명령문은 데이터 정의어(DDL)로서 실행 즉시 COMMIT되며, 데이터 조작어(DML)과 달리 ROLLBACK이 불가능하다.
- 생성된 테이블에 대한 모든 정보는 데이터 딕셔너리에 기록된다.
- GLOBAL TEMPORARY는 임시 테이블을 만들기 위해 사용되며, 테이블 구조는 모든 세션에서 볼 수 있지만, 데이터의 경우 테이블을 생성한 세션에서만 조회 가능하다.
- schema는 데이터베이스 사용자 계정과 같은 의미를 가진다고 할 수 있다.
- table명은 생성하고자 하는 테이블 이름을 의미.
- column은 테이블에 들어가는 칼럼(필드) 이름.
- datatype은 칼럼에 대한 데이터 타입과 길이를 나타냄(vachar, vachar2, int 등등..)
CREATE TABLE address
(
id NUMBER(3),
name VARCHAR2(50),
addr VARCHAR2(100),
phone VARCHAR2(30),
email VARCHAR2(100)
);서브쿼리를 이용한 테이블 생성 (select 해서 create table)
- 위의 내용처럼 CREATE TABLE 을 사용하여 테이블 생성 할 경우, 서브쿼리를 사용하여 다른 테이블의 구조를 복사할 수 있습니다.
- 만일 CREATE TABLE 명령문에서 칼럼 이름을 명시하지 않을 경우, 서브쿼리의 칼럼 이름과 동일한 칼럼 이름으로 새로운 테이블이 생성됩니다.
- 서브쿼리 절을 사용하여 테이블을 생성하면, 서브쿼리의 출력 결과가 새로운 테이블의 초기 데이터로 삽입됩니다.(출력 결과가 없는 경우, 서브쿼리에 사용된 필드들의 구조만 그대로 생성(복사)됨)
- 무결성 제약조건은 서브쿼리를 통해 NOT NULL 제약조건만 복사가능 합니다.(따라서 기본키, 참조키와 같은 무결성 제약조건은 테이블 복사(생성) 후 별도로 정의해주어야 함)
- DEFAULT 옵션에서 정의한 기본값은 서브쿼리를 통해 새로운 테이블에 그대로 복사 됨.
그럼 이제 예를 들어볼까요?
아래와 같이 주소록 테이블(adress)에 데이터를 삽입한 후, 이를 서브쿼리를 이용하여 새로운 테이블을 생성할 수 있습니다
-- 주소록 테이블에 데이터 삽입
INSERT INTO address VALUES (1, 'HGDONG', 'SEOUL', '123-3456', 'gdhong@dbcore.net');
COMMIT;
-- 서브쿼리를 이용하여 addr_second 테이블 생성
CREATE TABLE addr_second AS SELECT * FROM address;address 테이블에 데이터를 삽입한 후, addr_second 테이블을 서브쿼리를 통해 생성하고 있습니다. 이때, addr_second 테이블은 address 테이블의 구조와 데이터를 복사하여 새로운 테이블이 생성됩니다. (테이블구조, 데이터 모두 동일하게 생성됨)
서브쿼리를 이용하여 원하는 칼럼만 선택적으로 복사하여 테이블 생성
CREATE TABLE addr_third AS SELECT id,name FROM address;
확인
DESC addr_third
테이블 구조만 복사하여 생성(서브쿼리를 사용)
- WHERE 조건절에서 출력 결과값이 없도록, 항상 거짓이 되는 조건을 지정합니다. 예를 들어, WHERE 1=2와 같은 조건은 항상 거짓이 되므로 서브쿼리 절의 출력 결과가 하나도 발생하지 않습니다.
- 이렇게 서브쿼리를 사용하여 테이블을 생성하면 데이터는 복사되지 않고, 기존 테이블의 구조만을 새로운 테이블에 복사하게 됩니다.
주소록 테이블에서 id, name 칼럼만 복사하여 addr_fourth 테이블을 생성
-- 주소록 테이블에서 id, name 칼럼만 복사하여 addr_fourth 테이블 생성
CREATE TABLE addr_fourth
AS SELECT id, name FROM address
WHERE 1=2;address 테이블의 구조를 복사하여 addr_fourth 테이블을 생성하고 있습니다. WHERE 조건절에서 항상 거짓이 되도록 1=2라는 조건을 지정하여 실제 데이터는 복사되지 않도록 하고 addr_fourth 테이블에는 id와 name 칼럼만 존재하며 데이터는 포함되지 않음.
테이블 이름 변경, 테이블 삭제(컬럼 추가, 수정, 삭제)
테이블을 변경하거나 삭제하기 위해서는 ALTER TABLE, DROP TABLE을 사용합니다. 또한 칼럼 추가, 변경, 삭제를 통해 테이블을 유연하게 관리할 수 있습니다. 다만, DROP TABLE은 테이블과 함께 저장된 데이터까지 삭제되므로 삭제시 주의가 필요합니다.
테이블 이름 수정, 테이블 삭제(컬럼 추가, 수정, 삭제)
-- 칼럼 추가
ALTER TABLE address ADD (birth DATE);
-- 칼럼 삭제
ALTER TABLE address DROP COLUMN comments;
-- 컬럼 변경, 주소록 테이블에서 phone 칼럼의 데이터 타입의 크기를 50으로 증가
ALTER TABLE address MODIFY phone VARCHAR2(50);
-- 테이블 이름 변경
RENAME addr_second TO client_address;
-- 테이블 삭제
DROP TABLE addr_third;
TRUNCATE 사용하기
- 테이블 구조는 그대로 유지하면서 테이블 데이터와 테이블에 할당된 공간만 삭제하는 DDL(Data Definition Language) 명령문입니다.
- DELETE 명령문과 달리 TRUNCATE는 DML(Data Manipulation Language)이 아닌 DDL이므로 ROLLBACK이 불가능하며 삭제된 데이터를 복구할 수 없습니다.
- TRUNCATE는 WHERE 절에 의해 선택적으로 행을 삭제할 수 없습니다.
- 테이블에 생성된 제약조건, 인덱스, 뷰, 동의어는 삭제되지 않고 그대로 유지됩니다.
사용방법
TRUNCATE TABLE [schema.]table;테이블,컬럼(필드) 주석 추가하기
- 테이블이나 칼럼에 최대 2,000 바이트까지 주석을 추가할 수 있습니다.
- 추가된 주석은 데이터 딕셔너리를 이용하여 조회할 수 있습니다.
테이블에 주석 추가
COMMENT ON TABLE table명 IS '코멘트 내용 입력';예)
COMMENT ON TABLE address IS '고객 주소록을 관리하기 위한 테이블 입니다';칼럼에 주석 추가
COMMENT ON COLUMN table.column명 IS '주석 내용 입력';예)
COMMENT ON COLUMN address.name IS '고객 이름';위에서도 설명하였지만, 주의해야 할 점은 TRUNCATE의 경우 데이터 복구가 불가능하며 ROLLBACK을 사용할 수 없음.
데이터 딕셔너리
- 데이터 딕셔너리는 사용자와 데이터베이스 자원을 효율적으로 관리하기 위해 다양한 정보를 저장하는 시스템 테이블의 집합입니다.
- 데이터 딕셔너리는 테이블을 생성하거나 변경하는 등의 작업을 할 때 DB 서버에 의해 자동으로 갱신되는 테이블로데이터 딕셔너리에 저장된 데이터는 오라클 서버에서만 수정할 수 있으며, 데이터베이스 관리자나 일반 사용자는 직접 수정하거나 삭제 할 수 없으며 읽기 전용 뷰를 통해 데이터 딕셔너리의 내용을 조회할 수 있습니다(실제로 딕셔너리의 원 테이블은 조회해보면 암호화된 문자들이 보여짐.)
데이터 딕셔너리에서 관리하는 정보
- 데이터베이스의 물리적 구조와 객체의 논리적 구조
- 오라클 사용자 이름과 스키마 객체 이름
- 사용자에게 부여된 접근 권한과 롤
- 무결성 제약조건에 대한 정보
- 칼럼별로 지정된 기본 값
- 스키마 객체에 할당된 공간의 크기와 사용 중인 공간의 크기 정보
- 객체 접근 및 갱신에 대한 감사 정보
- 데이터베이스 이름, 버전, 생성 날짜, 시작 모드, 인스턴스 이름 정보 등
데이터 딕셔너리의 종류
데이터 딕셔너리 뷰는 대부분 읽기 전용이며 용도에 따라 USER, ALL, DBA의 3가지 접두어를 가지고 있습니다.
USER_ 데이터 딕셔너리 뷰
- 일반 사용자와 가장 밀접하게 관련된 뷰입니다.
- 사용자가 생성한 객체나 권한 정보를 조회할 수 있습니다.
- 예제:
USER_TABLES,USER_OBJECTS,USER_CATALOG등
ALL_ 데이터 딕셔너리 뷰
- 전체 사용자와 관련된 뷰로, 해당 객체의 소유자를 확인할 수 있는
OWNER칼럼이 있습니다. - 사용자가 접근 권한을 가진 객체에 대한 정보를 조회할 수 있습니다.
- 예제:
ALL_TABLES,ALL_OBJECTS등
DBA_ 데이터 딕셔너리 뷰
- 데이터베이스 시스템 관리와 관련된 뷰입니다.
- 데이터베이스 관리자나 SELECT ANY TABLE 시스템 권한을 가진 사용자만 접근할 수 있습니다.
- 주로 데이터베이스 관리와 자원 관리를 위해 사용됩니다.
- ex)
DBA_TABLES,DBA_OBJECTS등
ex) USER_TABLES 뷰를 사용하여 테이블의 이름, 테이블이 저장된 테이블스페이스 이름, 최소 확장영역 수와 최대 확장영역 수를 조회하는 쿼리
SELECT table_name, tablespace_name, min_extents, max_extents
FROM user_tables
WHERE table_name LIKE 'ADDR%';데이터 딕셔너리를 통해 사용자 및 데이터베이스 자원에 대한 다양한 정보를 조회할 수 있습니다.
맺으며
테이블 관리와 데이터 딕셔너리는 데이터베이스 운영에서 중요한 역할을 수행합니다. 테이블 생성, 변경, 삭제를 통해 데이터를 효율적으로 관리하고, 데이터 딕셔너리를 활용하여 객체 정보와 권한을 조회할 수 있습니다. 이를 통해 안정적이고 효율적인 데이터베이스 운영을 할 수 있는데요, 데이터베이스 자원을 최적화하고 데이터를 안전하게 관리하는데 항상 주의를 기울여야 하겠습니다.
이전 글 – 오라클 데이터 무결성 제약조건 정리 (기본키,외래키,참조키, 제약조건 생성, 추가, 삭제, 비활성화)
이전 글 – 오라클 인덱스에 대해 알아보자 (데이터베이스 실무에서 효율적으로 사용 할 수 있는 Oracle Index)
이전 글 – 오라클 뷰(VIEW) 조회 및 사용방법 (개념과 특징,종류, 장단점, 인라인뷰)
이전 글 – 오라클 사용자 권한 제어 (4가지 – 시스템 권한, 객체 권한, 롤, 동의어)
이전 글 – 오라클(데이터 웨어하우스) 분석 함수(효과적인 데이터 다차원 분석을 위한 강력한 도구)
이전 글 – 오라클 스케줄러와 JOB 관리 방법
이전 글 – 오라클 테이블 복구(테이블 데이터 복원하기)