sql 중급 / flashback query,table, drop
099 실수로 지운 데이터 복구하기 1(FLASHBACK QUERY)
(참고로 mysql은 플래시백 기능 없음)
과거의 데이터를 오라클은 확인할 수 있는데 그 기능이 바로 flashback query
-- 데이터를 삭제하시오
delete from emp;
commit;
rollaback;
select * from emp;위와 같은 상황이 벌어졌다면 flashback query로 과거의 데이터를 확인하고 복구하면 됨.
ㅇ복구방법
1. 현재시간 확인(반드시 오라클 프로그램의 시간을 확인할 것)
select systimestamp from dual;21/11/26 15:12:43.075000000 +09:00
2. 현재시간에서 10분전 emp테이블의 상태를 확인
select *
from emp
as of timestamp to_timestamp('21/11/26 15:12:33','RR/MM/DD HH24:MI:SS');3. 위 sql의 결과를 emp_past_data라는 이름으로 백업합니다.
create table emp_past_data
as
select *
from emp
as of timestamp to_timestamp('21/11/26 15:12:33','RR/MM/DD HH24:MI:SS');과거의 데이터를 볼 수 있는 시간의 기본값이 15분이므로 15분이 지나기전에 과거 데이터를 백업
문제551. 서브쿼리를 사용한 insert문을 이용해서 emp_past_data의 모든 데이터를 emp테이블로 입력하시오
insert into emp
select *
from emp_past_data;
commit;커밋까지 해야 데이터를 복구할 수 있음
100 실수로 지운 데이터 복구하기 2(FLASHBACK TABLE)
flashback table명령어로 실수로 지운 데이터 복구 가능
특정 과거 시점의 데이터로 테이블을 되돌려 놓는 것
1.데이터 삭제 후 현재 시간 확인
delete from emp14;
commit;
select systimestamp from dual;21/11/26 15:25:46.377000000 +09:00
2. emp14 테이블이 flashback이 가능한 상태인지 확인
select table_name, row_movement
from user_tables
where table_name = 'EMP14';현재 row_movement가 disable로 되어있는데, 이것을 enable 시켜야함. (enable상태여야 flashback이 가능함)
3. enable로 변경
alter table emp14 enable row movement;4. 21/11/26 15:24:46로 emp테이블을 되돌림
flashback table emp14 to timestamp to_timestamp('21/11/26 15:24:46','RR/MM/DD HH24:MI:SS');15분이 훨씬 지났는데도 복구가 가능한 이유는 삭제한 이후 db에 별 작업이 없어서 가능
101 실수로 지운 데이터 복구하기 3(FLASHBACK DROP)
drop된 테이블을 휴지통에서 복구하는 방법
예제
1. 사원테이블의 월급 인덱스를 생성합니다
create index emp_sal_idx
on emp(sal) ;2. emp테이블을 drop합니다
drop table emp;3. 휴지통 속에 있는 emp테이블을 확인합니다.
select object_name, original_name, droptime
from user_recyclebin;emp테이블을 drop하면서 emp테이블과 연관된 index도 같이 휴지통 속에 들어감.
4. 휴지통 속에 있는 emp테이블을 밖으로 복원하는 명령어
flashback table emp to before drop;5. emp_sal 인덱스도 복구가 되었는지 확인해봅니다.
select index_name
from user_indexes
where table_name = 'EMP';인덱스도 복구되었지만 인덱스 이름이 emp_sal_idx가 아니라 임의의 이름으로 변경되어 있음 > BIN$nT1a5JHLTIuJf1HtteSeBQ==$0
> 인덱스 이름을 원래 이름으로 변경하는 작업해야함.
6. 인덱스 이름 변경
alter index "BIN$nT1a5JHLTIuJf1HtteSeBQ==$0"
rename to emp_sal_idx;
-- 인덱스 이름 변경작업 확인
select index_name
from user_indexes
where table_name = 'EMP';인덱스를 여러개 만들어서 복원할 경우, 휴지통에서 조회했던 original_name이랑 대조해서 이름변경작업해야함.
문제552. 휴지통 비우기
purge recyclebin;문제553. dept테이블 loc에 인덱스를 생성하시오
create index dept_loc_idx
on dept(loc);문제554. dept테이블에 dname에 인덱스를 생성하시오
create index dept_dname_idx
on dept(dname);문제555. dept테이블을 drop하시오
drop table dept;문제556. dept테이블을 휴지통에서 복구하기 전에 dept테이블에 걸린 인덱스의 object_name과 original_name을 확인하시오
select object_name, original_name
from user_recyclebin;| BIN$+ubAjHiZQuSJGbQdvK9Ekg==$0 | DEPT_DNAME_IDX |
| BIN$OoFmZUb9QWq9eoqclhWd+A==$0 | DEPT_LOC_IDX |
이렇게 위와같이 따로 메모해 놓거나,
아니면 아래처럼 하나의 쿼리로 출력시켜버려도 됨.
select 'alter index "'|| object_name ||'" rename to '|| original_name ||';'
from user_recyclebin
where type = 'INDEX';alter index "BIN$+ubAjHiZQuSJGbQdvK9Ekg==$0" rename to DEPT_DNAME_IDX;
alter index "BIN$OoFmZUb9QWq9eoqclhWd+A==$0" rename to DEPT_LOC_IDX;
문제557. dept테이블을 휴지통에서 복원하시오
flashback table dept to before drop;문제558. dept테이블에 관련된 인덱스 이름을 원래대로 되돌리시오
alter index "BIN$+ubAjHiZQuSJGbQdvK9Ekg==$0" rename to DEPT_DNAME_IDX;
alter index "BIN$OoFmZUb9QWq9eoqclhWd+A==$0" rename to DEPT_LOC_IDX;
문제559. (오늘의 마지막 문제) 가정불화로 생기는 가장 큰 범죄유형은 무엇인가?
데이터 게시판 343번의 스크립트로 데이블과 데이터를 만드세요
select c.*, rank() over (partition by 범죄원인
order by 건수 desc) as 순위
from ( select *
from crime_cause
unpivot ( 건수 for 범죄원인 in (생계형,유흥,도막,허영심,복수,해고,징벌,가정불화,호기심,유혹,사고,불만,부주의,기타) )
) c
where 범죄원인 = '가정불화'
order by 순위 fetch first 1 rows with ties;