251031 TIL

FLASHBACK TABLE(10g)

• 삭제한 테이블을 복원하는 SQL문

삭제한 테이블 조회

SHOW recyclebin

SELECT * FROM user_recyclebin;

SELECT * FROM dba_recyclebin WHERE owner = 'HR';

삭제한 테이블 영구 삭제

PURGE recyclebin

테이블 삭제

DROP TABLE hr.emp;

SELECT * FROM user_recyclebin;

삭제 테이블 조회

SELECT * FROM "BIN$OCbOYC3eQU6vZWJizOBiBA==$0";

• user_recyclebin 에서 조회한 OBJECT_NAME을 테이블명으로 조회

테이블 복원

FLASHBACK TABLE emp TO BEFORE DROP;

• 테이블은 원래대로 복원되지만, 인덱스와 제약 조건은 이름이 바뀜 → RENAME 작업 해줘야 함

DROP TABLE hr.emp;

CREATE TABLE hr.emp
AS SELECT * FROM hr.employees;

SELECT * FROM user_recyclebin;

DROP TABLE hr.emp;

SELECT * FROM user_recyclebin;

FLASHBACK TABLE emp TO BEFORE DROP;

SELECT * FROM user_recyclebin;

• recyclebin에 동일한 테이블이 있는 경우 가장 최근에 삭제한 테이블을 복원
  • Stack 구조 (LIFO)

FLASHBACK TABLE emp TO BEFORE DROP;

• 복원해야 할 테이블 이름과 동일한 이름이 있을 경우 오류 발생

새로운 이름으로 복원

FLASHBACK TABLE emp TO BEFORE DROP RENAME TO emp_new;

테이블 영구히 삭제

DROP TABLE hr.emp_new PURGE;

특정한 테이블만 recyclebin에서 영구히 삭제

PURGE TABLE EMP;

TRUNCATE

• 테이블 행을 전부 삭제할 때 사용하는 SQL문
• DELETE문과 비슷하지만 TRUNCATE는 UNDO, REDO 발생량을 최소화하며 행을 지움
• DELETE문은 ROLLBACK이 가능하지만, TRUNCATE는 ROLLBACK 불가
• TRUNCATE는 MINEXTENT(1개)만 남기고, EXTENT를 지움

스토리지 관련 뷰

세그먼트

SELECT * FROM user_segments WHERE segment_name = 'EMP';
SELECT * FROM dba_segments WHERE segment_name = 'EMP' AND owner = 'HR';

익스텐트

SELECT * FROM user_extents WHERE segment_name = 'EMP';
SELECT * FROM dba_extents WHERE segment_name = 'EMP' AND owner = 'HR';

DELETE vs TRUNCATE

삭제 전

SELECT * FROM user_segments WHERE segment_name = 'EMP';
SELECT * FROM user_extents WHERE segment_name = 'EMP';

DELETE로 삭제

DELETE FROM hr.emp;
COMMIT;

SELECT * FROM user_segments WHERE segment_name = 'EMP';
SELECT * FROM user_extents WHERE segment_name = 'EMP';

• DELETE는 삭제해도 늘어난 스토리지 공간이 줄어들지 않음

TRUNCATE로 삭제

TRUNCATE TABLE hr.emp;

SELECT * FROM user_segments WHERE segment_name = 'EMP';
SELECT * FROM user_extents WHERE segment_name = 'EMP';

• TRUNCATE는 MINEXTENT만 남기고 EXTENT를 모두 지움

COMMENT

• 테이블과 컬럼의 주석(설명)을 만드는 SQL 문

테이블 주석 조회

SELECT * FROM user_tab_comments WHERE table_name = 'EMPLOYEES';
SELECT * FROM dba_tab_comments WHERE table_name = 'EMPLOYEES' AND owner = 'HR';

컬럼 주석 조회

SELECT * FROM user_col_comments WHERE table_name = 'EMPLOYEES';
SELECT * FROM dba_col_comments WHERE table_name = 'EMPLOYEES' AND owner = 'HR';

테이블 주석 생성

COMMENT ON TABLE hr.emp IS '사원 정보 테이블';
SELECT * FROM user_tab_comments WHERE table_name = 'EMP';

컬럼 주석 생성

COMMENT ON COLUMN hr.emp.employee_id IS '사원 번호';
COMMENT ON COLUMN hr.emp.salary IS '사원 급여';
SELECT * FROM user_col_comments WHERE table_name = 'EMP';

테이블 주석 삭제

COMMENT ON TABLE hr.emp IS '';
SELECT * FROM user_tab_comments WHERE table_name = 'EMP';

컬럼 주석 삭제

COMMENT ON COLUMN hr.emp.employee_id IS '';
COMMENT ON COLUMN hr.emp.salary IS '';
SELECT * FROM user_col_comments WHERE table_name = 'EMP';

VIEW

• 테이블의 뷰를 생성하여 데이터의 논리적 부분 집합 또는 조합을 나타냄
• SELECT문을 가지고 있는 객체
• 간접 access 제공

VIEW의 필요성

CREATE TABLE hr.emp
TABLESPACE users
AS
SELECT employee_id, last_name, department_id
FROM hr.employees;

SELECT * FROM hr.emp;

GRANT SELECT ON hr.emp TO insa;
SELECT * FROM user_tab_privs;

• 사용자마다 권한이 다르고 볼 수 있는 정보의 수준이 다름
• 사용자마다 권한에 맞는 데이터만 가진 테이블을 만들어주려면 스토리지가 낭비되고, 운영 관리 어려움
• VIEW를 통해 실제 테이블을 생성하지 않고, VIEW에 저장된 SELECT문을 통해 간접적으로 접근하도록 함

VIEW 생성

CREATE VIEW hr.emp_view
AS
SELECT employee_id, last_name, department_id
FROM hr.employees;

SELECT * FROM user_objects WHERE object_name = 'EMP_VIEW';

• OBJECT_TYPE이 VIEW로 생성됨

SELECT * FROM user_views WHERE view_name = 'EMP_VIEW';

• TEXT 컬럼에 SELECT 문을 가지고 있음
• VIEW를 조회하면 이 SELECT문을 실행해서 보여줌

권한이 있는 객체 정보 조회

SELECT * FROM all_objects WHERE object_name = 'EMP_VIEW';

권한이 있는 뷰 정보 조회

SELECT * FROM all_views WHERE view_name = 'EMP_VIEW';

VIEW 생성 조건

• CREATE VIEW 권한이 있어야 함

VIEW 삭제

DROP VIEW hr.emp_view;

• 뷰를 삭제하면 권한도 함께 회수되므로 삭제하기 전에 뷰에 대한 권한이 있는 유저를 체크해야 함
• 뷰 수정을 위해 삭제하고 다시 만드는 경우 권한도 다시 부여해야 함

VIEW 수정

CREATE OR REPLACE VIEW hr.emp_view
AS
SELECT employee_id, last_name, department_id
FROM hr.employees;

• 기존 뷰가 있으면 지우고 생성
• 객체 권한은 그대로 살아있음
• 뷰를 수정할 때는 CREATE OR REPLACE VIEW를 사용하는 것이 좋음

단순 뷰

• 하나의 테이블에서만 데이터를 가지고 온다. 즉 조인이 없다.
• 함수 또는 데이터에 대해 그룹을 포함하지 않는 경우
• 뷰를 통해서 dml 작업을 수행할 수 있음

DML 권한 부여

GRANT INSERT, UPDATE, DELETE ON hr.emp_view TO insa;
SELECT * FROM user_tab_privs;

• 뷰를 통해 insert, update, delete 가능
• 입력 시 뷰에 없는 컬럼은 NULL로 입력
• NOT NULL 제약조건이 있는 컬럼을 뷰가 가지고 있지 않으면 INSERT 권한이 있어도 입력 불가

• 뷰의 컬럼이 가공된 컬럼이라면 INSERT, UPDATE 불가

복합 뷰

• 여러 테이블에서 데이터를 가져옴, 즉 조인이 있는 경우
• 함수, 데이터 그룹을 사용하는 경우
• 뷰를 통한 DML 작업 수행 불가
• 복합 뷰이지만 DML 작업을 수행하려면 트리거 프로그램으로 구현하면 됨

뷰에 CHECK 제약 조건 생성

CREATE OR REPLACE VIEW hr.emp_view
AS
SELECT id, name, dept_id
FROM hr.emp
WHERE dept_id = 20
WITH CHECK OPTION CONSTRAINT emp_view_ck;

SELECT * FROM user_constraints WHERE table_name = 'EMP_VIEW';

• WHERE 절이 CHECK 제약 조건

CHECK 제약 조건에 의한 오류 발생

READ ONLY 뷰 생성

CREATE OR REPLACE VIEW hr.emp_view
AS
SELECT id, name, dept_id
FROM hr.emp
WITH READ ONLY;

• WITH READ ONLY : 단순 뷰이지만 DML 작업은 불허해야 하는 경우(SELECT 만 가능)