231020 Oracle SQL FLASHBACK TABLE, TRUNCATE, COMMENT. TABLE 지우기, 복원하기

■ FLASHBACK TABLE (10g)
    A. 삭제한 테이블을 복원하는 SQL 문

# 휴지통 비우기

purge recyclebin;

# test table 생성

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

# test table DROP 하기

DROP TABLE hr.emp_copy;

# DROP 한 TABEL 확인.

SELECT * FROM hr.emp_copy;

 

# 휴지통 보이기

show recyclebin;
/*
ORIGINAL NAME RECYCLEBIN NAME                OBJECT TYPE DROP TIME           
------------- ------------------------------ ----------- ------------------- 
EMP_COPY      BIN$RMNEYpstSmC2X6wSABimCw==$0 TABLE       2023-10-20:12:07:36
*/

 

# show recyclebin; 기능을 SELECT 문으로 보기

SELECT * FROM user_recyclebin;

# hr.emp_copy 테이블의 data와 동일하게 조회. "BIN$RMNEYpstSmC2X6wSABimCw==$0"

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

# 삭제한 테이블 복원 : "Flashback을(를) 성공했습니다."

FLASHBACK TABLE emp_copy TO BEFORE DROP;

# 복원된 테이블 확인

SELECT * FROM hr.emp_copy;

< 순서 확인 >
1# test 테이블 생성

CREATE TABLE hr.emp (
    id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY , 
    name VARCHAR2(30) CONSTRAINT emp_name_nn NOT NULL , 
    sal NUMBER , 
    dept_id NUMBER CONSTRAINT emp_dept_id_fk REFERENCES hr.dept(dept_id) , 
    CONSTRAINT emp_name_uk UNIQUE(name), 
    CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000)
);

2# 휴지통 비우기 + DROP 하기

purge recyclebin;

DROP TABLE hr.emp;

3#_1) 휴지통 보이기

show recyclebin; 
/*
ORIGINAL NAME RECYCLEBIN NAME                OBJECT TYPE DROP TIME           
------------- ------------------------------ ----------- ------------------- 
EMP_ID_PK     BIN$sfQBpLf0Seu0WfUoEY9FYg==$0 INDEX       2023-10-20:12:14:38 
EMP_NAME_UK   BIN$Vkb8TjizTHuFOBvQ28UXmA==$0 INDEX       2023-10-20:12:14:38 
EMP           BIN$ez0QWE8TQz2uCIPxliKi4Q==$0 TABLE       2023-10-20:12:14:38 
*/

3#_2) show recyclebin; 기능을 SELECT 문으로 보기

SELECT * FROM user_recyclebin;

4# 삭제한 테이블 복원 : "Flashback을(를) 성공했습니다."

FLASHBACK TABLE emp TO BEFORE DROP;

5#_1) 복원된 테이블 확인

SELECT * FROM hr.emp;

5#_2) 제약조건 확인하기.

SELECT * FROM user_constraints WHERE table_name = 'EMP';
SELECT * FROM user_cons_columns WHERE table_name = 'EMP';
SELECT * FROM user_indexes WHERE table_name = 'EMP';
SELECT * FROM user_ind_columns WHERE table_name = 'EMP';

6#) 복원된 테이블 확인후 "constraints_name", "index_name" 수정
1) 컬러명 이름 수정

ALTER TABLE hr.emp RENAME CONSTRAINT emp_id_pk TO name;
ALTER TABLE hr.emp RENAME CONSTRAINT name TO sal;
ALTER TABLE hr.emp RENAME CONSTRAINT "BIN$tnFroqdRTUK/ubc5nvZSTg==$0" TO emp_id_pk;
ALTER TABLE hr.emp RENAME CONSTRAINT "BIN$WFyVtWXdQD+dsd13pUI0vg==$0" TO emp_name_uk;

2) INDEX 이름 수정

ALTER INDEX "BIN$sfQBpLf0Seu0WfUoEY9FYg==$0" RENAME TO id;
ALTER INDEX id RENAME TO emp_id_pk;
ALTER INDEX "BIN$Vkb8TjizTHuFOBvQ28UXmA==$0" RENAME TO emp_name_uk;

< EX >

PURGE RECYCLEBIN;

# test table 생성

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

# table 확인

SELECT * FROM hr.emp_test;

# table 삭제

DROP TABLE hr.emp_test;

# 휴지통 보기

SHOW RECYCLEBIN;
/*
ORIGINAL NAME RECYCLEBIN NAME                OBJECT TYPE DROP TIME           
------------- ------------------------------ ----------- ------------------- 
EMP_TEST      BIN$FjpS5/c1RJGX1gYjAzrghA==$0 TABLE       2023-10-20:13:53:20 
*/

# 'SHOW RECYCLEBIN;'를 SELECT 문으로 확인.

SELECT * FROM user_recyclebin;

# hr.emp_test 확인

SELECT * FROM hr.emp_test;
/* 오류발생 : table 존재하지 않음 */

# 동일한 이름으로 test table 생성

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

# table 확인

SELECT * FROM hr.emp_test;

# table 삭제

DROP TABLE hr.emp_test;

# 휴지통 보기

SHOW RECYCLEBIN;
/*
ORIGINAL NAME RECYCLEBIN NAME                OBJECT TYPE DROP TIME           
------------- ------------------------------ ----------- ------------------- 
EMP_TEST      BIN$FjpS5/c1RJGX1gYjAzrghA==$0 TABLE       2023-10-20:13:53:20 
EMP_TEST      BIN$9aLktxoTTJOrignNF3Ygyg==$0 TABLE       2023-10-20:13:57:11 
*/

# 'SHOW RECYCLEBIN;'를 SELECT 문으로 확인.

SELECT * FROM user_recyclebin;

# table 복구
    - recyclebin 에 동일한 이름의 테이블이 있을 경우 '가장 최근의 삭제한 테이블'을 복원한다.

FLASHBACK TABLE emp_test TO BEFORE DROP;

# table 복원 확인. 

SELECT * FROM hr.emp_test;

# 'SHOW RECYCLEBIN;', SELECT 문으로 확인.

SHOW RECYCLEBIN;
/*
ORIGINAL NAME RECYCLEBIN NAME                OBJECT TYPE DROP TIME           
------------- ------------------------------ ----------- ------------------- 
EMP_TEST      BIN$FjpS5/c1RJGX1gYjAzrghA==$0 TABLE       2023-10-20:13:53:20 
*/
SELECT * FROM user_recyclebin;

# table 복원하면서 RENAME 하기.
    - 복원해야 할 테이블이 이름이 이미 존재하는 경우, 새로운 이름으로 복원할 수 있다.

FLASHBACK TABLE emp_test TO BEFORE DROP RENAME TO emp_2023;

# 복원한 table 확인

SELECT * FROM hr.emp_2023;

# table 영구히 삭제

DROP TABLE hr.emp_2023 PURGE;

■ TRUNCATE ex) 쇼핑몰 - 데일리 테이블 사용.
    A. 테이블의 행을 전부 삭제하는 SQL 문
    B. DELETE 문과 비슷하나, TRUNCATE 문은 저장공간을 초기화 상태로 만든다. 
    C. TRUNCATE 문은 ROLLBACK 불가하니 주의할 것.

# 테이블의 data 삭제

TRUNCATE TABLE hr.emp_test;

# 테이블의 구조만 확인.

SELECT * FROM hr.emp_test;

■ COMMENT 
    A. 테이블과 컬럼의 주석(설명) 만드는 SQL 문

# 테이블 주석 확인.

SELECT * FROM user_tab_comments WHERE table_name = 'EMPLOYEES';

# 컬럼 주석 확인.

SELECT * FROM user_col_comments WHERE table_name = 'EMPLOYEES';

# 테이블 주석 생성

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

# 컬럼 주석 생성

COMMENT ON COLUMN hr.emp.id 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.id IS '';
SELECT * FROM user_col_comments WHERE table_name = 'EMP';