오라클 테이블 관리와 데이터 딕셔너리

11. 테이블 관리와 데이터 딕셔너리

테이블 생성

테이블 이름 정의 규칙

-테이블과 칼럼 이름은 문자(A-Z a-z)로 시작해야 하며 30자 이내로 가능하다

-테이블 이름은 문자, 숫자,특수문자(_, $, #)를 사용할 수 있다.

-테이블 이름은 대소문자를 구분하지 않으나 데이터 딕셔너리에는 대문자로 저장된다. 만일 -테이블 이름을 소문자로 저장할 경우에는 단일 인용부호를 (‘’) 이용한다

-테이블 이름은 동일한 사용자가 소유한 다른 객체의 이름과 중복될 수 없다.

-서로 다른 테이블에서 동일한 데이터를 저장하는 칼럼이름은 가능하면 같은 이름을 사용하는 것이 좋다.

-필요에 따라 언제든지 테이블 생성이 가능하다. 하지만 설계도에 의해 건물을 짓는것처럼 사전에 완성된 설계도에 따라 테이블을 생성하는 것이 좋다.

CREATE TABLE 명령문은 실행즉시 COMMIT되는 데어티 정의어(DDL)이며 데이터 조작어(DML)과 달리 ROLLBACK될 수 없다.생성된 테이블에 대한 모든 정보는 데이터 딕셔너리에 기록된다.

사용법

CREATE [ GLOBAL TEMPORARY] TABLE [schema.]table

(column datatype [DEFAULT expression] [column_constraint clause]

[,...]);

GLOBAL TEMPORARY : 임시 테이블을 만들기 위한 키워드로서 테이블 구조는 모든 세션에서 볼 수 있지만, 데이터는 테이블을 생성한 세션에서만 조회 가능

schema : 데이터베이스 사용자 계정과 같은 의미

table : 생성하고 자 하는 테이브 이름

column : 테이블에 포함되는 칼럼 이름

datatype : 칼럼에 대한 데이터 타입과 길이

DEFAULT expression : 데이터 입력시 값이 생략된 경우에 입력되는 기본 값

column_constratint_clause : 칼럼에 대해 정의되는 무결성 제약조건

연락처 정보를 저장하기 위한 주소록 테이블 생성

CREATE TABLE address

(id NUMBER(3),

name VARCHAR2(50),

addr VARCHAR2(100),

phone VARCHAR2(30),

email VARCHAR2(100));

SELECT * FROM TAB;

DESC address

DEFAULT 옵션

칼럼의 입력값이 생략될 경우에 NULL 대신에 입력되는 기본 값을 지정하기 위한 기능.

(칼럼이름이나 의사칼럼(NEXTVAL, CURRVAL)은 사용할 수 없음-그리고 기본 값의 데이터 타입은 칼럼의 데이터 타입과 일치해야 함)

칼럼 정의시 기본 값 설정 ex) addr VARCHAR2(100) DEFAULT ‘KOREA’

테이블 생성 확인

DESC[RIBE] 명령어는 테이블의 생성 여부와 테이블의 구조를 확인하기 위한 명령어이다.

확인 가능한 정보는 칼럼이름,데이터타입과 크기, NOT NULL 무결성 제약조건이다.

ex) DESC student

서브쿼리를 이용한 테이블 생성

CREATE TABLE 명령문에서 서브쿼리 절을 이용하여 다른 테이블의 구조와 데이터를 복사하여 새로운 테이블을 생성할 수 있다. CREATE TABLE 명령문에서지정한 칼럼수와 데이터 타입은 서브쿼리 절에서 지정한 칼럼수와 데이터 타입이 일치해야 하며 칼럼이름은 달라도 된다.

만일 CREATE TABLE 명령문에서 칼럼이름을 명시하지 않을 경우에는 서브쿼리의 칼럼 이름과 동일한 칼럼 이름으로 생성된다. 서브 쿼리 절을 이용하여 테이블을 생성하면 서브쿼리의 출력 결과가 초기 데이터로 삽입된다. 그리고 무결성 제약조건은 NOT NULL 조건만 복사되므로 기본키, 참조키 와 같은 무결성 제약조건은 사용자가 다시 정의해야 한다.

DEFAULT 옵션에서 정의한 기본값은 그대로 복사된다.

사용법

CREATE TABLE table

[column[, column…]]

AS subquery;

서브쿼리를 이용한 새로운 테이블 생성방법 설명을 위한 예제데이터 입력

INSERT INTO address VALUES (1, 'HGDONG', 'SEOUL', '123-3456', 'gdhong@dbcore.net');

COMMIT;

서브쿼리 절을 이용하여 주소록 테이블의 구조와 데이터를 복사하여 addr_second 테이블을 생성하여라

CREATE TABLE addr_second

(id, name, addr, phone, email)

AS SELECT * FROM address;

email을 e-mail로 바꾸니 에러발생 (되야 정상-확인필)

DESC addr_second

서브쿼리를 이용하여 새로운 테이블 생성시 기존 테이블에서 원하는 칼럼만 선택적으로 복사하여 생성할 수 있다.

CREATE TABLE addr_third AS SELECT id,name FROM address;

DESC addr_third

테이블 구조 복사

서브쿼리를 이용한 테이블 생성시 데이터는 복사하지 않고 기존 테이블의 구조만 복사할 수 있다.

서브쿼리의 WHERE 조건절에서 출력 결과 집합이 생성되지 않도록 항상 거짓이 되는 조건을 지정하면 된다.(ex WHERE 1=2와같은 조건은 항상 거짓이 되어 서브쿼리절의 출력 결과가 하나도 발생하지 않는다)

사용법

CREATE TABLE table

AS SELECT *

FROM source_table

WHERE condition;

condition : 출력 결과가 항상 거짓인 조건을 명시 (ex WHERE 1=2)

주소록 테이블에서 id, name 칼럼만 복사하여 addr_fourth 테이블을 생성하여라, 단 데이터는 복사하지 않는다.

CREATE TABLE addr_fourth

AS SELECT id, name FROM address

WHERE 1=2;

테이블 구조 변경

ALTER TABLE 명령문은 기존 테이블의 구조를 변경하기 위한 DDL 명령문이다.

칼럼 추가

기존 테이블에 새로운 칼럼을 추가. 추가된 칼럼은 테이블의 마지막 부분에 생성되며 사용자가 칼럼의 위치를 지정할 수 없다. 추가된 칼럼에도 기본값은 지정할 수 있다. 기존 데이터가 존재하면 추가된 칼럼값은 NULL로 입력되고 새로 입력되는 데이터에 대해서만 기본값이 적용된다

사용법

ALTER TABLE table

ADD (column datatype [DEFAULT expression]

[, column datatype]....);

주소록 테이블에 날짜 타입을 가지는 birth 칼럼을 추가

ALTER TABLE address ADD (birth date);

DESC address

주소록 테이블에 문자 타입을 가지는 comment칼럼 추가, 기본값은 No Commnet로 지정

ALTER TABLE address

ADD (comments varchar2(200)

DEFAULT 'No Comment');

DESC address

칼럼 삭제

ALTER TABLE … DROP COLUMN 명령문은 테이블에서 특정 칼럼을 삭제하기 위한 DDL 명령문이다. 칼럼을 삭제하면 해당 칼럼에 저장된 데이터가 함께 삭제되어 복구가 불가능 하므로 주의해야 한다.

칼럼은 2개 이상의 칼럼이 존재하는 테이블에서만 삭제할 수 있다. 그리고 하나의 명령문은 하나의 칼럼만 삭제할 수 있다.

사용법

ALTER TABLE table DROP COLUMN column;

주소록 테이블에서 comments 칼럼을 삭제

ALTER TABLE address DROP COLUMN comments;

DESC address

칼럼 변경

ALTER TABLE..MODIFY 명령문은 테이블에서 칼럼을 변경하기 위한 DDL 명령문이다.칼럼 변경을 통해 데이터타입,크기 기본값을 변경할 수 있다. 변경 대상 칼럼에 기존 데이터가 없는 경우에는 데이터 타입이나 크기의 변경을 자유롭게 할 수 있다. 하지만 데이터가 존재하는 경우에 데이터 타입의 변경은 CHAR와 VARCHAR2 내에서만가능하다. 또한 칼럼 크기의 변경은 저장된 데이터의 크기보다 같거나 클 경우에만 가능하다. 숫자 타입에서는 정밀도가 증가 할 수 있다. 기본 값의 변경은 변경후에 입력되는 데이터부터 적용된다.

사용법

ALTER TALBE table

MODIFY (column datatype [DEFAULT expression]

[, column datatype]...);

주소록 테이블에서 phone 칼럼의 데이터 타입의 크기를 50으로 증가하여라.

ALTER TABLE address MODIFY phone VARCHAR2(50);

DESC address

테이블 이름 변경

RENAME 명령문은 객체의 이름을 변경하기 위한 DDL 명령문이다 . RENAME명령문을 사용하여 뷰, 시퀀스, 동의어 등과 같은 DB객체 이름을 변경 할 수 있다.

사용법

RENAME old_table TO new_table

addr_second 테이블 이름을 client_address로 변경하여라

RENAME addr_second TO client_address;

SELECT * FROM tab;

테이블 삭제

DROP TABLE 명령문은 기존 테이블을 삭제하기 위한 DDL 명령문이다.

기존 테이블을 삭제하면 테이블에 저장된 데이터가 함꼐 삭제되어 복구가 불가능하다. 또한 삭제된 테이블의 칼럼에 대해 생성된 인덱스도 함께 삭제된다. 그리고 삭제된 테이블과 관련된 뷰와 동의어는 ‘invalid’ 상태가 된다.

만일 삭제하고자 하는 테이블의 기본키나 고유 키를 다른 테이블에서 참조하고 있는경우에는 해당 테이블을 삭제할 수 없다. 이 경우에는 참조하는 테이블을 먼저 삭제한 후에 삭제하거나 CASCADE CONSTRAINTS 옵션을 사용하여 무결성 제약조건을 동시에 삭제하면 된다.

사용법

DROP TABLE [ schema.] table [cascade constraints]

cascade constraints : 삭제 대상 테이블의 기본 키나 고유 키를 참조하는 무결성 제약조건을 동시에 삭제하기 위한 옵션.

addr_third 테이블을 삭제하여라

DROP TABLE addr_third;

select * from tab;

TRUNCATE 명령문

테이블 구조는 그대로 유지하면서 테이블 데이터와 테이블에 할당된 공간만 삭제하는 DDL 명령문이다. DELETE 명령문은 DML인 관계로 ROLLBACK이 가능하지만 TRUNCATE 는 DDL인 관계로 ROLLBACK이 불가능하여 삭제된 데이터의 복구가 불가능하므로 주의해야 한다. 또한 TRUNCATE는 WHERE절에 의해 선택적으로 행을 삭제할 수 없다. 하지만 테이블에 생성된 제약조건과 테이블과 연관된 인덱스, 뷰 , 동의어는 그대로 유지된다.

TRUNCATE TABLE [schema.]table

client_address 테이블의 데이터와 할당된 공간을 삭제하여라

SELECT * FROM client_address;

TRUNCATE TABLE client_address;

SELECT * FROM client_address;

ROLLBACK; (롤백하여도 삭제된 데이터 복구 불가능)

SELECT * FROM client_address;

주석 추가

테이블이나 칼럼에 최대 2,000 바이트까지 주석을 추가 할 수 있다.

추가된 주석은 ALL_COL_COMMENTS, USER_TAB_COMMENTS 와 같은 데이터 딕셔너리를 이용하여 조회할 수 있다.

사용법

COMMENT ON TABLE table IS ‘content of comment’;

주소록 테이블에 ‘고객 주소록을 관리하기 위한 테이블’ 이라는 주석을 추가

COMMENT ON TABLE address IS ‘고객 주소록을 관리하기 위한 테이블’;

칼럼에 주석 추가

사용법

COMMENT ON COLUMN table.column IS ‘content of comment’;

주소록 테이블의 name 칼럼에 고객이름 이라는 주석 추가

COMMENT ON COLUMN address.name IS ‘고객 이름’;

데이터 딕셔너리

사용자와 데이터베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블의 집합.

데이터 딕셔너리에 저장된 데이터 수정은 오라클 서버만 가능하고, 데이터베이스 관리자나 일반 사용자는 읽기 전용 뷰에 의해 데이터 딕셔너리의 내용을 조회만 할 수 있다.

*데이터 딕셔너리에서 관리하는 정보

-DB의 물리적 구조와 객체의 논리적 구조

-오라클 사용자 이름과 스키마 객체 이름

-사용자에게 부여된 접근 권한과 롤

-무결성 제약조건에 대한 정보

-칼럼별로 지정된 기본 값

-스키마 객체에 할당된 공간의 크기와 사용중인 공간의 크기 정보

-객체 접근 및 갱신에 대한 감사 정보

-데이터베이스 이름, 버전, 생성날짜, 시작모드, 인스턴스 이름 정보

데이터 딕셔너리의 종류

데이터 딕셔너리는 읽기 전용 뷰로 구성되어 디비 관리자나 사용자에게 데이터 딕셔너리에 저장된 정보의 조회를 허용한다. 데이터 딕셔너리 뷰는 용도에 따라 USER, ALL, DBA의 3가지 접두어를 가지고 있다,

USER접두어를 가진 데이터 딕셔너리 뷰는 해당 객체를 소유한 사용자만 접근 가능하고, ALL 접두어를 가진 데이터 딕셔너리 뷰는 소유자나 소유자로부터 접근 권한을 부여받은 사용자만 접근할 수 있다. DBA 접두어를 가진 데이터 딕셔너리 뷰는 데이터베이스 관리자만 접근할 수 있다. 또한 디비 관리자는 모든 종류의 데이터 딕셔너리 뷰를 접근할 수 있다.

USER_ 데이터 딕셔너리 뷰

일반 사용자와 가장 밀접하게 관련된 뷰이다.

사용자는 USER_ 데이터 딕셔너리 뷰를 이용하여 자신이 생성한 테이블, 인덱스, 뷰, 동의어 등과 같은 객체나 해당 사용자에게 부여된 권한정보를 조회할 수 있다.

USER_ 데이터 딕셔너리 뷰 조회 예

SELECT table_name FROM user_tables;

user_tables는 사용자가 소유한 테이블에 대한 정보를 조회할 수 있는 데이터 딕셔너리 뷰.

ALL_ 데이터 딕셔너리 뷰

전체 사용자와 관련된 뷰이다. ALL_ 데이터 딕셔너리 뷰는 해당 객체의 소유자를 확인할 수 있는 OWNER칼럼이 있다. 사용자는 ALL_데이터 딕셔너리 뷰를 이용하여 해당 사용자가 접근할 수 있는 모든 객체에 대한 정보를 조회할 수 있다.

ALL_ 데이터 딕셔너리 뷰 조회 예

SELECT owner, table_name FROM all_tables;

all_tables는 자기 소유 또는 권한을 부여받은 테이블에 대한 정보를 조회할 수 있는 데이터 딕셔너리 뷰이다.

DBA_ 데이터 딕셔너리 뷰

데이터베이스 시스템 관리와 관련된 뷰이다. DBA_ 데이터 딕셔너리 뷰는 DBA나 SELECT ANY TABLE 시스템 권한을 가진 사용자만 접근할 수 있다. 이 뷰는 주로 데이터베이스 관리자가 사용자의 접근 권한이나 데이터베이스 자원을 관리하기 위한 목적으로 사용된다.

DBA_ 데이터 딕셔너리 뷰 조회 예

SELECT owner, table_name FROM dba_tables;

dba_tables는 모든 사용자가 소유한 테이블에 대한 정보를 조회할 수 있는 데이터 딕셔너리 뷰이다.

사용자 테이블 정보 조회

사용자가 생성한 테이블에 대한 정보는 USER_TABLES, USER_OBJECTS, USER_CATALOG(CAT)등과 같은 데이터 딕셔너리 뷰를 통해 확인할 수 있다.

USER_TABLES

테이블이 저장된 테이블스페이스 이름, 데이터가 저장된 물리적 공간, 그리고 블록 파라미터 정보 등과 같은 정보를 저장한다.

테이블 이름이 ADDR로 시작하는 테이블의 이름, 테이블이 저장된 테이블스페이스 이름, 최소 확장영역 수와 최대 확장영역 수를 출력하여라

SELECT table_name, tablespace_name, min_extents, max_extents

FROM user_tables

WHERE table_name LIKE 'ADDR%';

*테이블스페이스

: 오라클에서 가장 큰 논리적인 저장 단위이다. (오라클의 논리적인 저장단위의 종류는 세그먼트, 확장영역, 블록 등이 있다.)

USER_OBJECTS

사용자가 생성한 테이블 정보와 함께 인덱스, 시퀀스, 동의어 , 뷰 등과 같은 객체에 대한 이름, 종류, 생성 날짜 등의 정보를 저장한다.

객체의 종류가 테이블이고 이름이 ADDR로 시작하는 객체의 이름, 종류, 생성날짜를 출력하여라

SELECT object_name, object_type, created FROM user_objects WHERE object_name LIKE 'ADDR%' AND object_type='TABLE';

USER_CATALOG

사용자 소유로 생성된 모든 객체 이름과 객체 종류에 대한 정보를 저장한다. table.name칼럼은 테이블 이름이 아니라 객체 이름이고 table_type칼럼은 객체의 종류를 의미한다.

DESC user_catalog

SELECT * FROM user_catalog;