Oracle 테이블 생성및 관리하기

CREATE TABLESPACE TABLESPACE2
DATAFILE 'D:\test\TEST_TBS1.DBF' SIZE 10M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED -- 추가되는 용량
LOGGING                                 -- 로깅 사용
EXTENT MANAGEMENT LOCAL AUTOALLOCATE    -- LOCAL
BLOCKSIZE 8K                            -- 블록 크기 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

-- table space rename 
ALTER TABLESPACE TABLESPACE2
RENAME TO TEST_TBS_NEW;

-- 파일 용량 수정
ALTER DATABASE
DATAFILE 'D:\TEST\TEST_TBS1.DBF' RESIZE 20M;

-- TABLE SPACE 삭제
DROP TABLESPACE TABLESPACE1
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;

-- TABLE 생성
CREATE TABLE TB_TEST01(
    COL_01 VARCHAR2(10),
    COL_02 VARCHAR2(10),
    COL_03 VARCHAR2(10),
    COL_04 VARCHAR2(10)
);

CREATE TABLE TB_TEST02(
    COL_01 VARCHAR2(10),
    COL_02 VARCHAR2(10),
    COL_03 VARCHAR2(10),
    COL_04 VARCHAR2(10)
)
TABLESPACE TABLESPACE2;

-- TABLE COPY : 데이터를 포함
CREATE TABLE TB_TEST03
AS
SELECT *
FROM jobs;

SELECT * FROM tb_test03;

DROP TABLE tb_test03;

CREATE TABLE TB_TEST03
AS
SELECT job_id AS "업무번호", job_title AS "업무명"
FROM jobs;

CREATE TABLE TB_GROUP_DEPT
AS
SELECT department_id AS "부서번호", SUM(salary) AS "합계", AVG(salary) AS "평균"
FROM employees
GROUP BY department_id;

SELECT * FROM TB_GROUP_DEPT;

-- TABLE COPY : 데이터 미포함
CREATE TABLE TB_TEST04
AS
SELECT *
FROM departments
WHERE 1=2;      -- 성립할 수 없는 조건

SELECT * FROM TB_TEST04;

-- TABLE : CREATE, ALTER, DROP
-- DATA : INSERT, UPDATE, DELETE, SELECT

-- TABLE 수정 : 테이블명 수정
ALTER TABLE TB_TEST04
RENAME TO TB_TEST99;

SELECT * FROM TB_TEST99;

-- TABLE 수정 : 단일 컬럼 추가
ALTER TABLE TB_TEST99
ADD
LOCATION_NAME VARCHAR2(30);

-- TABLE 수정 : 다중 컬럼 추가
ALTER TABLE TB_TEST99
ADD
(COL_01 VARCHAR2(30), COL_02 NUMBER(5,2));

-- TABLE 수정 : 단일 컬럼 수정
ALTER TABLE TB_TEST99
MODIFY
COL_01 VARCHAR2(20);

ALTER TABLE TB_TEST99
MODIFY
(COL_01 VARCHAR2(20), COL_02 NUMBER(4));

-- TABLE 수정 : 컬럼 삭제
ALTER TABLE TB_TEST99
DROP COLUMN LOCATION_NAME;

ALTER TABLE TB_TEST99
DROP
(COL_01, COL_02);

-- TABLE 수정 : 컬럼명 수정
ALTER TABLE TB_TEST99
RENAME 
COLUMN DEPARTMENT_ID TO "부서번호";

DROP TABLE TB_TEST01;

DROP TABLE TB_TEST02
CASCADE CONSTRAINTS;

-- 휴지통에서 복구
FLASHBACK TABLE TB_TEST02 
TO BEFORE DROP;

-- 휴지통 비우기
PURGE RECYCLEBIN;