· 4분 읽기

KH_ORACLE_07_DDL

ORACLE

목록 보기숨기기7/12
  1. KH_ORACLE_01_SQL의 기본
  2. KH_ORACLE_02_SELECT로 특정 데이터 추출하기
  3. KH_ORACLE_03_SQL 주요 함수
  4. KH_ORACLE_04_그룹 쿼리와 집합 연산자
  5. KH_ORACLE_05_조인
  6. KH_ORACLE_06_서브쿼리
  7. KH_ORACLE_07_DDL
  8. KH_ORACLE_08_DML
  9. KH_ORACLE_09_트랜잭션 관리, 데이터읽기일관성과 락(Lock)
  10. KH_ORACLE_10_데이터 무결성을 위한 제약 조건
  11. KH_ORACLE_11_가상 테이블인 뷰
  12. KH_ORACLE_12_시퀀스, 인덱스
‹ 이전다음 ›

DDL의 종류에는 CREATE, ALTER, DROP, TRUNCATE + RENAME이 있다.

테이블의 구조를 정의하는 CREATE TABLE

CREATE TABLE 명령어는 새로운 테이블을 생성한다.

-- 형식
CREATE TABLE table_name
(column_name data_type expr, …);
-- 예
CREATE TABLE EX2_1 (
 COLUMN1 CHAR(10),
 COLUMN2 VARCHAR2(10),
 COLUMN3 VARCHAR2(10),
 COLUMN4 NUMBER
);

식별자 명명 규칙

테이블 명과 컬럼 명과 같이 사용자가 이름을 부여하는 것을 식별자라고 하는데 식별자를 부여하기 위해서는 규칙을 준수해야 한다.

 - 반드시 문자로 시작해야 함

 - A-Z까지의 대소문자와 0-9까지의 숫자, 특수 기호는 (_,$,#)만 포함 가능

 - 오라클에서 사용되는 예약어나 다른 객체명과 중복 불가

 - 공백 허용 안함

테이블의 구조만 복사하기

테이블의 구조만 복사하는 것은 별도의 명령이 있는 것이 아니다. 이 역시 서브 쿼리를 이용해야 하는데 WHERE 조건 절에 항상 거짓이 되는 조건을 지정하게 되면 테이블에서 얻어질 수 있는 로우가 없게 되므로 빈 테이블이 생성되게 된다.

예를 들면 WHERE 1=0; 조건은 항상 거짓이다. 이를 이용하여 테이블의 데이터는 가져오지 않고 구조만 복사하게 된다.

CREATE TABLE EMP06
AS
SELECT * FROM EMP WHERE 1=0;

테이블 구조를 변경하는 ALTER TABLE

새로운 컬럼 추가하기

ALTER TABLE ADD 문은 기존 테이블에 새로운 컬럼을 추가한다.

새로운 컬럼은 테이블 맨 마지막에 추가되므로 자신이 원하는 위치에 만들어 넣을 수 있다.

이미 이전에 추가해 놓은 로우가 존재한다면 그 로우에도 컬럼이 추가되지만, 컬럼 값은 NULL 값으로 입력된다.

-- 형식
ALTER TABLE table_name
ADD (column_name, data_type expr, …);

기존 컬럼 속성 변경하기

ALTER TABLE MODIFY 문을 다음과 같은 형식으로 사용하면 테이블에 이미 존재하는 컬럼을 변경할 수 있게 된다.

컬럼을 변경한다는 것은 컬럼에 대해서 데이터 타입이나 크기, 기본 값들을 변경한다는 의미이다.

-- 형식
ALTER TABLE table_name
MODIFY (column_name, data_type expr, …);

-- 예
ALTER TABLE EMP01
MODIFY(JOB VARCHAR2(30));
DESC EMP01

기존 컬럼 삭제하기

ALTER TABLE ~ DROP COLUMN 명령어로 칼럼을 삭제할 수 있다.

-- 형식
ALTER TABLE table_name
DROP COLUMN column_name;
-- 예
ALTER TABLE EMP01
DROP COLUMN JOB;
DESC EMP01

SET UNUSED 옵션 적용하기

특정 테이블에서 컬럼을 삭제하는 경우 다음과 같이 무조건 삭제하는 것은 위험하다.

테이블에 저장된 내용이 많을 경우 해당 테이블에서 컬럼을 삭제하는 데 꽤 오랜 시간이 걸리게 될 것이다. 컬럼을 삭제하는 동안에 다른 사용자가 해당 컬럼을 사용하려고 접근하게 되면 지금 현재 테이블이 사용되고 있기 때문에 다른 사용자는 해당 테이블을 이용할 수 없게 된다. 이런 경우 작업이 원활하게 진행되지 않고 락이 발생하게 된다.

ALTER TABLE 에 SET UNUSED 옵션을 지정하면 컬럼을 삭제하는 것은 아니지만 컬럼의 사용을 논리적으로 제한할 수 있게 된다.

SET UNUSED 옵션은 사용을 논리적으로 제한할 뿐 실제로 컬럼을 삭제하지 않기 때문에 작업 시간이 오래 걸리지 않는다. 그렇기 때문에 락이 걸리는 일도 일어나지 않게 된다.

테이블 구조를 삭제하는 DROP TABLE

DROP TABLE 문은 기존 테이블을 제거한다.

-- 형식
DROP TABLE table_name;
-- 예
DROP TABLE EMP01;
DROP TABLE EMP01 CASCADE CONSTRAINTS;
SELECT * FROM EMP01;

테이블의 모든 로우를 제거하는 TRUNCATE

기존에 사용하던 테이블의 모든 로우를 제거하기 위한 명령어로 TRUNCATE 가 제공된다.

-- 형식
TRUCATE TABLE
table_name
-- 예
SELECT * FROM EMP02;
TRUNCATE TABLE EMP02;
SELECT * FROM EMP02;

테이블명을 변경하는 RENAME

기존에 사용하던 테이블의 이름을 변경하기 위한 명령어로 RENAME이 제공된다.

-- 형식
RENAME old_name TO new_name
-- 예
RENAME EMP02 TO TEST;
SELECT * FROM EMP02;
SELECT * FROM TEST;

데이터 딕셔너리와 데이터 딕셔너리 뷰

데이터베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블을 데이터 딕셔너리라고 한다.

데이터 딕셔너리는 사용자가 테이블을 생성하거나 사용자를 변경하는 등의 작업을 할 때 데이터베이스 서버에 의해 자동으로 갱신되는 테이블로 사용자는 데이터 딕셔너리의 내용을 직접 수정하거나 삭제할 수 없다.

USER_ 데이터 딕셔너리

접두어로 USER 가 붙은 데이터 딕셔너리는 자신의 계정이 소유한 객체 등에 관한 정보를 조회한다.

DESC USER_TABLES;
SHOW USER
SELECT TABLE_NAME FROM USER_TABLES
ORDER BY TABLE_NAME DESC;

ALL_데이터 딕셔너리

사용자 계정이 소유한 객체는 자신의 소유이므로 당연히 접근이 가능하다.

그러나 만일 자신의 계정이 아닌 다른 계정 소유의 테이블이나 시퀀스 등은 어떨까?

오라클에서는 타계정의 객체는 원천적으로 접근 불가능하다.

하지만 그 객체의 소유자가 접근할 수 있도록 권한(GRANT)을 부여하면 타 계정의 객체에도 접근이 가능하다.

ALL_ 데이터 딕셔너리 뷰는 현재 계정이 접근 가능한 객체, 즉 자신 계정의 소유이거나 접근 권한을 부여 받은 타계정의 객체 등을 조회할 수 있는 데이터 딕셔너리 뷰이다.

아래는 현재 계정이 접근 가능한 테이블의 정보를 조회하는 뷰이다.

DESC ALL_TABLES;
SELECT OWNER, TABLE_NAME FROM ALL_TABLES;

DBA_ 데이터 딕셔너리 뷰

DBA_ 데이터 딕셔너리는 DBA가 접근 가능한 객체 등을 조회할 수 있는 뷰이다.

앞서도 언급했지만 DBA가 접근 불가능한 정보는 없기에 데이터베이스에 있는 모든 객체 등의 의미라 할 수 있다.

USER_ 와 ALL_ 와 달리 DBA_ 데이터딕셔너리뷰는 DBA 시스템 권한을 가진 사용자만 접근할 수 있다.

SELECT TABLE_NAME, OWNER
FROM DBA_TABLES;
CONN system/manager
SELECT TABLE_NAME, OWNER
FROM DBA_TABLES;

댓글

댓글을 불러오는 중…