adminschool.net wiki

이 문서는 읽기 전용입니다. 원본을 볼 수는 있지만 바꿀 수는 없습니다. 문제가 있다고 생각하면 관리자에게 문의하세요.
======= flashback =======

===== 확인 =====
  - <code> $ sqlplus / as sysdba </code>
  - <code> SQL> select instance_name from v$instance; </code>
  - <code> SQL> show user </code>
  - <code> export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'</code>
==== HR 계정 활성화 ====
  - <code> SQL> alter user hr identified by oracle account unlock; </code>
  - <code> SQL> grant execute on dbms_flashback to hr; </code>
==== HR 계정으로 접속 ====
  - <code> sqlplus hr/oracle </code>
  - <code> SQL> select systimestamp from dual; </code>

==== HR 계정으로 AS OF SCN ====
  - <code> SQL> create table emp2 as select * from emp; </code>
  - <code> SQL> select dbms_flashback.get_system_change_number() from dual; </code>
  - <code> SQL> select count(*) from emp2; </code>
  - <code> SQL> delete from emp2; </code>
  - <code> SQL> select count(*) from emp2; </code>
  - <code> SQL> select count(*) from emp2 as of scn <scn_number>; </code>
  - <code> SQL> insert into emp2 select * from emp2 as of scn <scn_number>; </code>
  - 
==== HR 계정으로 AS OF TIMESTAMP ====
  - <code> SQL> select systimestamp from dual; </code>
  - <code> SQL> select count(1) from emp2; </code>
  - <code> SQL> delete from emp2 where deptno=30; </code>
  - <code> SQL> commit; </code>
  - <code> SQL> select systimestamp from dual; </code> 
  - <code> SQL> select count(*) from emp2; </code>
  - <code> SQL> select count(*) from emp2 
as of timestamp(to_timestamp('2015-06-20 16:42:00','YYYY-MM-DD HH24:MI:SS')); </code> 
  - <code> 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; </code> 
  - <code> 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; </code> 


==== HR 계정 flashback version query ====
  - <code>
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;

</code>
adminschool.net wiki

사용자 도구

사이트 도구

문서 도구
