[ORACLE] 테이블 생성과 관리를 위한 DDL 작성 1

in Study on Oracle
[ORACLE] 테이블 생성과 관리를 위한 DDL 작성 1

테이블 생성과 관리를 위한 DDL 작성 1

데이터베이스 오브젝트

오브젝트설명
Table여러 행 데이터를 저장하는 기본적인 저장 단위
View하나 이상의 테이블 데이터를 이용한 보조집합을 통해 논리적으로 표현
Sequence숫자 값 생성기
Index일부 쿼리의 조회 성능을 향상시키기 위한 객체
Synonym동의어, 객체에 부여하는 또다른 이름

객체 및 열 이름 규칙

테이블 및 열은 다음의 규칙을 준수해야 한다

  • 문자로 시작해야 한다
  • 길이는 1-30자 사이여야 한다
  • A-Z, a-z, 0-9, _, $, #만 포함할 수 있다 +한글
  • 동일한 유저가 소유한 다른 객체의 이름과 중복되지 않아야 한다
    • Schema = user = ID = owner
  • Oracle 서버 에약어(키워드)는 사용할 수 없다

CREATE TABLE 구문

  • 사용자는 다음 권한이 필요 :
    • CREATE TABLE 권한
    • 저장소(TABLESPACE) 사용 권한 = Quota
      CREATE  TABLE [schema.]table
          (column datatype [DEFAULT expr][, ...]);
    
  -- 최대 칼럼의 갯수는 1000개로 제한된다.
  • 구문 필수 요소 :
    • 테이블 명
    • 칼럼 명
    • 칼럼 데이터 타입
    • 칼럼 크기
      GRANT  CREATE TABLE TO hr2;
  ALTER  USER hr2 1m ON example;
  -- hr2 유저에 테이블을 생성하는 권한 부여
  -- hr2 유저에게 example 테이블스페이스의 1메가를 사용할 수 있도록 변경
  -- 1m 대신 unlimited를 쓰면 무제한의 용량 사용 가능
    
  CREATE  TABLE hr2.product
  (
  	prod_id number(10),
  	prod_name varchar2(30),
  	prod_date date
  )
    
  desc product;

    테이블 스페이스는 다음 쿼리로 확인할 수 있다.

      SELECT  name
    FROM  v$tablespace;

다른 사용자의 테이블 참조 방법

  • 다른 유저가 소유한 테이블은 유저의 스키마에 있다
  • 이러한 테이블에는 소유자의 이름을 접두어로 사용해야 한다
    • object 권한도 필요
      SELECT  empno, ename, sal
    FROM  scott.emp
   WHERE  empno=7788;

DEFAULT 옵션

  • 삽입 시 열의 기본값을 지정
... hire_date DATE DEFAULT SYSDATE, ...
  • 리터럴 값, 표현식 또는 SQL 함수 등을 사용할 수 있다.
  • 다른 열의 이름이나 의사 열은 사용 불가
  • 기본 데이터 유형은 데이터 유형과 일치해야 한다
CREATE  TABLE hire_date
(
	id NUMBER(8),
	hire_date DATE DEFAULT SYSDATE
);

데이터 타입

Data Type설명
VARCHAR2 (size)가변길이 문자 데이터 (up to 4000byte)
CHAR (size)고정길이 문자 데이터 (up to 2000byte)
NUMBER (p, s)가변길이 숫자 데이터 (up to 38자리)
DATEDate and time 값
LONG가변길이 문자 데이터 (up to 2GB)
CLOB문자 데이터 (up to 4GB)
RAW AND LONG 
RAW원시 바이너리 데이터
BLOB바이너리 데이터 (up to 4GB)
BFILE바이너리 값을 저장한 외부 파일 (up to 4GB)
ROWIDbase-64로 인코딩된 시스템 설정 값으로 테이블 안에 있는 행의 고유 주소값

Subquery를 이용한 테이블 생성

  • CREATE TABLE 문과 AS subquery 옵션을 결합하여 테이블을 생성하고 행을 삽입
CREATE  TABLE table 
	[(column, column...)]
    AS  subquery;
  • 지정된 열 갯수와 subquery 열 개수를 일치시킵니다
  • 열 이름과 기본값을 가진 열을 정의합니다
CREATE  TABLE dept80
    AS
        SELECT  employee_id, last_name, salary*12 ANNSAL, hire_date
          FROM  employees
         WHERE  department_id = 80;

-- salary*12에 Alias로 ANNSAL을 써주지 않으면 칼럼명으로 '*'은 들어갈 수 없기 때문에 오류 발생

ALTER TABLE 구문

ALTER TABLE 구문을 사용한 칼럼 추가, 변경, 삭제 :

 ALTER  TABLE table
   ADD  (column datatype [DEFAULT expr]
        [, column datatype]...);

 ALTER  TABLE table
MODIFY
        (column datatype [DEFAULT expr]
        [, column datatype]...);

ALTER  TABLE table
 DROP  (column [, column] ...);

칼럼 추가

  • ADD 절을 사용하여 추가
ALTER  TABLE dept80
  ADD  (job_id VARCHAR2 (9));
  • 새 열은 마지막 열로 지정 :
 EMPLOYEE_IDLAST_NAMEANNSALHIRE_DATEJOB_ID
1145Russell1400001-OCT-96(null)
2146Partners1350005-JAN-97(null)
3147Ezzrazuriz1200010-MAR-97(null)

칼럼 수정

  • 열의 데이터 유형, 크기 및 기본값을 변경할 수 있다
 ALTER  TABLE dept80
MODIFY  (last_name VARCHAR2(30));
  • 기본값 변경은 이후에 테이블에 삽입하는 항목에만 적용된다
  • 데이터 타입 크기를 줄일 때 그 크기보다 큰 데이터가 저장되어 있을 경우, 에러가 발생

칼럼 삭제

  • DROP COLUMN 절을 사용하여 테이블에서 더 이상 필요 없는 열을 삭제할 수 있다
ALTER  TABLE dept80
 DROP  COLUMN job_id;

SET UNUSED 옵션

  • SET UNUSED 옵션을 사용하여 하나 이상의 열을 unused로 표시
  • DROP UNUSED COLUMNS 옵션을 사용하여 unused로 표시된 열을 제거할 수 있다
ALTER  TABLE <table_name>
  SET  UNUSED (<column_name> [, <column_name>]);
--OR
ALTER  TABLE <table_name>
  SET  UNUSED COLUMN <column_name> [, <column_name>];

ALTER  TABLE <table_name>
 DROP  UNUSED COLUMNS;

실제로 칼럼을 드랍하는 업무를 수행하게 될 때, DROP 을 사용하여 칼럼을 삭제하려 할 경우 해당 데이터가 많을 때 지연이 길게 발생하여 해당 테이블에 대한 접근이 길게 지연될 수 있다.

따라서 이럴 경우, 우선 해당 칼럼을 SET UNUSED 옵션을 사용하여 비활성화 시키고, 한가할 때 DROP 시키도록 하자.

테이블 삭제

  • 테이블을 Recycle bin으로 이동
  • PURGE 절이 지정되면 테이블 및 해당 데이터를 완전히 제거
  • 종속 개체 무효화 및 테이블의 객체 권한 제거
DROP  TABLE dept80;

Oracle 에서는 DROP 으로 테이블을 삭제해도 완전히 삭제가 되지 않고 Recycle bin으로 이동된다.

  DROP  TABLE product3;

SELECT  *
  FROM  product3; -- 오류 발생

SELECT  *
  FROM  user_recyclebin; -- 완전히 지워지진 않고 recyclebin에서 확인 가능
 OBJECT NAMEORIGINAL_NAMEOPERATION…
1BIN$xsJpnm…PRODUCT3DROP…

Recycle bin으로 이동된 테이블을 오브젝트 이름으로 조회할 수 있다.

SELECT  *
  FROM  "BIN$xsJpnmXNOfzgU2U4qMDpwg==$0";
 PROD_IDPROD_NAMEPROD_DATE
1100unknown21/07/10

Recycle bin으로 이동된 테이블은 복구가 가능하다.

FLASHBACK  TABLE product3 TO BEFORE DROP;

또한 DROP 시에 Recycle bin으로 이동하지 않고 바로 삭제도 가능하다.

DROP  TABLE product3 PURGE;

© 2022. All rights reserved. 신동민의 블로그