SQL> SHOW PARAMETER UNDO
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
--undo_management: UNDO SEGMENT의 관리
AUTO는 자동관리
//플래쉬백 쿼리를 쓰려면 undo_management가 반드시 AUTO해야된다
--undo_retention : COMMIT후 DATA 지속시간
//현재 10800
SQL> SELECT 10800/60/60 FROM DUAL;
10800/60/60
-----------
3
1 개의 행이 선택되었습니다.
--10800: 3시간까지 이다.
//시간 변경 하면 15 분 후부터 적용
SQL> DESC DBMS_FLASHBACK
PROCEDURE DISABLE
PROCEDURE ENABLE_AT_SYSTEM_CHANGE_NUMBER
인수명 유형 기본 내부/외부?
------------------------------ ----------------------- --------- --------
QUERY_SCN NUMBER IN
PROCEDURE ENABLE_AT_TIME
인수명 유형 기본 내부/외부?
------------------------------ ----------------------- --------- --------
QUERY_TIME TIMESTAMP IN
FUNCTION GET_SYSTEM_CHANGE_NUMBER RETURNS NUMBER
-- FLASHBACK
실행할수 있는 권한이 있어야한다.
//SCOTT 실수로 EMP테이블을 삭제한다.
--SCOTT
C:\Documents and Settings\easy>SQLPLUS SCOTT/TIGER
SQL*Plus: Release 9.2.0.1.0 - Production on 월 Jun 16 15:47:56 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
다음에 접속됨:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> DELETE EMP;
14 행이 삭제되었습니다.
SQL> COMMIT;
커밋이 완료되었습니다.
SQL> @TIME
TO_CHAR(SYSDATE,'YY
-------------------
2008-06-16:15:48:37
SQL> SELECT * FROM EMP;
선택된 레코드가 없습니다.
//SYS는 SCOTT이 삭제한지 모르고 SCOTT의 EMP테이블을 조회해본다
--SYS
SQL> SHOW USER
USER은 "SYS"입니다.
SQL> SELECT * FROM SCOTT.EMP
2 ;
선택된 레코드가 없습니다. //나올리가없다;
-- FLASHBACK
실행할수 있는 권한이 있어야한다.
SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO SCOTT;
권한이 부여되었습니다.
//SCOTT접속
SQL> CONN SCOTT/TIGER
연결되었습니다.
SQL> DESC DBMS_FLASHBACK
PROCEDURE DISABLE
PROCEDURE ENABLE_AT_SYSTEM_CHANGE_NUMBER
인수명 유형 기본 내부/외부?
------------------------------ ----------------------- --------- --------
QUERY_SCN NUMBER IN
PROCEDURE ENABLE_AT_TIME
인수명 유형 기본 내부/외부?
------------------------------ ----------------------- --------- --------
QUERY_TIME TIMESTAMP IN
FUNCTION GET_SYSTEM_CHANGE_NUMBER RETURNS NUMBER
SQL> SELECT * FROM SCOTT.EMP;
선택된 레코드가 없습니다.
//타임머신을 타고 EMP테이블이 삭제하기 전시간으로 돌아간다고 생각하면된다.
SQL> EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP(-
> '2008/06/16:15:43:37','YYYY/MM/DD:HH24:MI:SS'));
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT * FROM SCOTT.EMP; //현재시간은 삭제하기 전 시간이다.
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 80/12/17 800
20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300
30
7521 WARD SALESMAN 7698 81/02/22 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 81/04/02 2975
20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400
30
7698 BLAKE MANAGER 7839 81/05/01 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 81/06/09 2450
10
7788 SCOTT ANALYST 7566 87/04/19 3000
20
7839 KING PRESIDENT 81/11/17 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 81/09/08 1500 0
30
7876 ADAMS CLERK 7788 87/05/23 1100
20
7900 JAMES CLERK 7698 81/12/03 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 81/12/03 3000
20
7934 MILLER CLERK 7782 82/01/23 1300
10
14 개의 행이 선택되었습니다.
//DELETE하기 전시간이라 테이블이 보인다.복구된 것은 아니다.
--다시돌아오기:EXECUTE DBMS_FLASHBACK.DISABLE();
SQL> EXECUTE DBMS_FLASHBACK.DISABLE();
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT * FROM SCOTT.EMP;// 원래시간으로 돌아왔기때문에 테이블이 보이지않는다.
선택된 레코드가 없습니다.
//다시 삭제전 시간으로
SQL> EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP('2008/06/16:15:43:37','YYYY/MM/DD:HH24:MI:SS
'));
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT * FROM SCOTT.EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 80/12/17 800
20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300
30
7521 WARD SALESMAN 7698 81/02/22 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 81/04/02 2975
20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400
30
7698 BLAKE MANAGER 7839 81/05/01 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 81/06/09 2450
10
7788 SCOTT ANALYST 7566 87/04/19 3000
20
7839 KING PRESIDENT 81/11/17 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 81/09/08 1500 0
30
7876 ADAMS CLERK 7788 87/05/23 1100
20
7900 JAMES CLERK 7698 81/12/03 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 81/12/03 3000
20
7934 MILLER CLERK 7782 82/01/23 1300
10
14 개의 행이 선택되었습니다.
SQL> @TIME
TO_CHAR(SYSDATE,'YY
-------------------
2008-06-16:16:02:29
1 개의 행이 선택되었습니다.
//원래시간으로
SQL> EXECUTE DBMS_FLASHBACK.DISABLE();
PL/SQL 처리가 정상적으로 완료되었습니다.
//그렇다면 EMP테이블을 복구할려면 FLASH BACK을 사용해 삭제전 과거의 테이블을
불러와서 현재의 EMP테이블에 넣어주면된다
--복구
SQL> INSERT INTO EMP
2 SELECT * FROM EMP AS OF TIMESTAMP(TO_TIMESTAMP(
3 '2008/06/16:15:43:37','YYYY/MM/DD:HH24:MI:SS'));
14 개의 행이 만들어졌습니다.
SQL> SELECT * FROM EMP; //정상조회된다.
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 80/12/17 800
20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300
30
7521 WARD SALESMAN 7698 81/02/22 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 81/04/02 2975
20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400
30
7698 BLAKE MANAGER 7839 81/05/01 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 81/06/09 2450
10
7788 SCOTT ANALYST 7566 87/04/19 3000
20
7839 KING PRESIDENT 81/11/17 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 81/09/08 1500 0
30
7876 ADAMS CLERK 7788 87/05/23 1100
20
7900 JAMES CLERK 7698 81/12/03 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 81/12/03 3000
20
7934 MILLER CLERK 7782 82/01/23 1300
10
14 개의 행이 선택되었습니다.
SQL> COMMIT;
커밋이 완료되었습니다.
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 80/12/17 800
20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300
30
7521 WARD SALESMAN 7698 81/02/22 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 81/04/02 2975
20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400
30
7698 BLAKE MANAGER 7839 81/05/01 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 81/06/09 2450
10
7788 SCOTT ANALYST 7566 87/04/19 3000
20
7839 KING PRESIDENT 81/11/17 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 81/09/08 1500 0
30
7876 ADAMS CLERK 7788 87/05/23 1100
20
7900 JAMES CLERK 7698 81/12/03 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 81/12/03 3000
20
7934 MILLER CLERK 7782 82/01/23 1300
10
14 개의 행이 선택되었습니다.
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 80/12/17 800
20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300
30
7521 WARD SALESMAN 7698 81/02/22 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 81/04/02 2975
20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400
30
7698 BLAKE MANAGER 7839 81/05/01 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 81/06/09 2450
10
7788 SCOTT ANALYST 7566 87/04/19 3000
20
7839 KING PRESIDENT 81/11/17 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 81/09/08 1500 0
30
7876 ADAMS CLERK 7788 87/05/23 1100
20
7900 JAMES CLERK 7698 81/12/03 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 81/12/03 3000
20
7934 MILLER CLERK 7782 82/01/23 1300
10
14 개의 행이 선택되었습니다.
SQL>
