목차

flashback

확인

  1.  $ sqlplus / as sysdba 
  2.  SQL> select instance_name from v$instance; 
  3.  SQL> show user 
  4.  export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

HR 계정 활성화

  1.  SQL> alter user hr identified by oracle account unlock; 
  2.  SQL> grant execute on dbms_flashback to hr; 

HR 계정으로 접속

  1.  sqlplus hr/oracle 
  2.  SQL> select systimestamp from dual; 

HR 계정으로 AS OF SCN

  1.  SQL> create table emp2 as select * from emp; 
  2.  SQL> select dbms_flashback.get_system_change_number() from dual; 
  3.  SQL> select count(*) from emp2; 
  4.  SQL> delete from emp2; 
  5.  SQL> select count(*) from emp2; 
  6.  SQL> select count(*) from emp2 as of scn <scn_number>; 
  7.  SQL> insert into emp2 select * from emp2 as of scn <scn_number>; 

HR 계정으로 AS OF TIMESTAMP

  1.  SQL> select systimestamp from dual; 
  2.  SQL> select count(1) from emp2; 
  3.  SQL> delete from emp2 where deptno=30; 
  4.  SQL> commit; 
  5.  SQL> select systimestamp from dual; 
  6.  SQL> select count(*) from emp2; 
  7.  SQL> select count(*) from emp2 
    as of timestamp(to_timestamp('2015-06-20 16:42:00','YYYY-MM-DD HH24:MI:SS')); 
  8.  SQL> select count(*) from emp2 
    as of timestamp(to_timestamp('2015-06-20 16:42:00','YYYY-MM-DD HH24:MI:SS')) where deptno=30; 
  9.  SQL> insert into emp2 select * from emp2 
    as of timestamp(to_timestamp('2015-06-20 16:42:00','YYYY-MM-DD HH24:MI:SS')) where deptno=30; 

HR 계정 flashback version query

  1. SQL> create table emp as select * from scott.emp;
    
    SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
    2015-06-20 16:57:28
    
    SQL> update emp set comm=1234 where empno=7782;
    SQL> commit;
    
    SQL> update emp set comm=3456 where empno=7782;
    SQL> rollback;
    
    SQL> update emp set comm=5678 where empno=7782;
    SQL> commit;
    
    SQL> update emp set comm=7890 where empno=7782;
    SQL> commit;
    
    SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
    2015-06-20 17:01:08
    
    SQL>
    set lines 200
    column ename format a10
    column start_time format a20
    column end_time format a20
    
    SELECT empno, ename, sal, comm, deptno,
        versions_xid AS XID,
        versions_operation as operation,
        versions_startscn AS START_SCN,
        versions_starttime as start_time,
        versions_endscn AS END_SCN,
        versions_endtime as end_time
    FROM emp VERSIONS BETWEEN TIMESTAMP
            TO_TIMESTAMP('2015-06-20 16:57:27','YYYY-MM-DD HH24:MI:SS')
       AND TO_TIMESTAMP('2015-06-20 17:01:09','YYYY-MM-DD HH24:MI:SS')
    WHERE empno=7782
    order by versions_starttime nulls first;