목차

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;