[11/07] Flashback query / recyclebin rename

flashback query

HR 스키마의 특정 데이터를 삭제한 후에 과거의 삭제한 데이터를 보는 뷰를 만들어라

HR@orcl>create table cp_emp as select * from employees;

Table created.

=====================================================

제약 조건이 있어서 삭제가 안될 경우에는 해당 제약 조건을 disable 시킨다

HR@RCATDB03>delete employees where employee_id = 100;

delete employees where employee_id = 100

*

ERROR at line 1:

ORA-02292: integrity constraint (HR.DEPT_MGR_FK) violated - child record found

HR@RCATDB03>alter table departments disable constraint DEPT_MGR_FK;

Table altered.

HR@RCATDB03>alter table employees disable constraint EMP_MANAGER_FK;

Table altered.

====================================================

HR@orcl>delete cp_emp where employee_id = 100;

1 row deleted.

HR@orcl>select systimestamp from dual;

SYSTIMESTAMP

---------------------------------------------------------------------------

07-NOV-15 04.09.23.309117 PM +09:00

HR@orcl>commit;

Commit complete.

HR@orcl>select * from cp_emp

  2  as of timestamp(to_timestamp('2015-11-07 16:09:23','yyyy-mm-dd HH24:MI:SS')) where employee_id = 100;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                    PHONE_NUMBER          HIRE_DATE           JOB_ID         SALARY

----------- -------------------- ------------------------- ------------------------- -------------------- ------------------- ---------- ----------

COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

-------------- ---------- -------------

        100 Steven               King                      SKING                    515.123.4567          2003-06-17:00:00:00 AD_PRES         30000

                                     90

HR@orcl>create view emp_time as select * from cp_emp

  2  as of timestamp(to_timestamp('2015-11-07 16:09:23','yyyy-mm-dd HH24:MI:SS')) where employee_id = 100;

View created.

HR@orcl>select * from emp_time;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                    PHONE_NUMBER          HIRE_DATE           JOB_ID         SALARY

----------- -------------------- ------------------------- ------------------------- -------------------- ------------------- ---------- ----------

COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

-------------- ---------- -------------

        100 Steven               King                      SKING                    515.123.4567          2003-06-17:00:00:00 AD_PRES         30000

                                     90

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

flashback drop table rename recover

HR@orcl>drop table cp_emp;

Table dropped.

HR@orcl>show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

CP_EMP           BIN$I+8iVuoj4JPgUAB/AQA97g==$0 TABLE        2015-11-07:16:16:08

HR@orcl>flashback table cp_emp to before drop rename to cp_emp1;

Flashback complete.