閃回刪除、閃回查詢

一、閃回刪除
實驗步驟：
利用閃回刪除恢復被刪除的job_history表；

操作如下：
（1） 刪除HR使用者中的JOB_HISTORY表； 
SYS@GMDBA> conn hr/oracle
Connected.
HR@GMDBA> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMPLOYEES_TEST TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
9 rows selected.
HR@GMDBA> drop table JOB_HISTORY;
Table dropped.
HR@GMDBA> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$7JztDH4gOM/gQwpQqMChGg==$0 TABLE
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMPLOYEES_TEST TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
LOCATIONS TABLE
REGIONS TABLE
9 rows selected.
（2） 進行閃回刪除；
HR@GMDBA> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ --------------
JOB_HISTORY BIN$7JztDH4gOM/gQwpQqMChGg==$0 TABLE 2013-12-03:16:17:24 


HR@GMDBA> flashback table "BIN$7JztDH4gOM/gQwpQqMChGg==$0" to before drop;
Flashback complete.
HR@GMDBA> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMPLOYEES_TEST TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
9 rows selected.

二、閃回查詢
實驗步驟：
1.刪除hr使用者下的job_history表的資料；
2.使用閃回查詢檢視刪除前的資料；
3.利用閃回刪除恢復刪除的資料；

操作如下：
（1） 查詢hr使用者下的job_history表資料；
SYS@GMDBA> conn hr/oracle
Connected.
HR@GMDBA> select * from job_history;
EMPLOYEE_ID START_DAT END_DATE JOB_ID DEPARTMENT_ID
----------- --------- --------- ---------- -------------
102 13-JAN-01 24-JUL-06 IT_PROG 60
101 21-SEP-97 27-OCT-01 AC_ACCOUNT 110
101 28-OCT-01 15-MAR-05 AC_MGR 110 
201 17-FEB-04 19-DEC-07 MK_REP 20
114 24-MAR-06 31-DEC-07 ST_CLERK 50
122 01-JAN-07 31-DEC-07 ST_CLERK 50
200 17-SEP-95 17-JUN-01 AD_ASST 90
176 24-MAR-06 31-DEC-06 SA_REP 80
176 01-JAN-07 31-DEC-07 SA_MAN 80
200 01-JUL-02 31-DEC-06 AC_ACCOUNT 90
10 rows selected.
（2） 確定當前時間；
HR@GMDBA> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
HR@GMDBA> select sysdate from dual;
SYSDATE
-------------------
2013-12-03 22:59:37
（3） 刪除hr使用者下的job_history表資料；
HR@GMDBA> delete from job_history;
10 rows deleted.
HR@GMDBA> commit;
Commit complete.
HR@GMDBA> select * from job_history;
no rows selected
（4） 利用閃回查詢檢視刪除前的資料；
HR@GMDBA> select * from job_history as of timestamp to_timestamp('2013-12-03 22:59:37','yyyy-mm-dd hh24:mi:ss');
EMPLOYEE_ID START_DAT END_DATE JOB_ID DEPARTMENT_ID
----------- --------- --------- ---------- -------------
102 13-JAN-01 24-JUL-06 IT_PROG 60
101 21-SEP-97 27-OCT-01 AC_ACCOUNT 110
101 28-OCT-01 15-MAR-05 AC_MGR 110 
201 17-FEB-04 19-DEC-07 MK_REP 20
114 24-MAR-06 31-DEC-07 ST_CLERK 50
122 01-JAN-07 31-DEC-07 ST_CLERK 50
200 17-SEP-95 17-JUN-01 AD_ASST 90
176 24-MAR-06 31-DEC-06 SA_REP 80
176 01-JAN-07 31-DEC-07 SA_MAN 80
200 01-JUL-02 31-DEC-06 AC_ACCOUNT 90
10 rows selected.
（5） 把資料插回job_history表中；
HR@GMDBA> insert into job_history select * from job_history as of timestamp to_timestamp('2013-12-03 22:59:37','yyyy-mm-dd hh24:mi:ss');
10 rows created.
HR@GMDBA> select * from job_history;
EMPLOYEE_ID START_DAT END_DATE JOB_ID DEPARTMENT_ID
----------- --------- --------- ---------- -------------
102 13-JAN-01 24-JUL-06 IT_PROG 60
101 21-SEP-97 27-OCT-01 AC_ACCOUNT 110
101 28-OCT-01 15-MAR-05 AC_MGR 110
201 17-FEB-04 19-DEC-07 MK_REP 20
114 24-MAR-06 31-DEC-07 ST_CLERK 50
122 01-JAN-07 31-DEC-07 ST_CLERK 50
200 17-SEP-95 17-JUN-01 AD_ASST 90
176 24-MAR-06 31-DEC-06 SA_REP 80
176 01-JAN-07 31-DEC-07 SA_MAN 80
200 01-JUL-02 31-DEC-06 AC_ACCOUNT 90
10 rows selected.
HR@GMDBA> commit;
Commit complete.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29319205/viewspace-1062222/，如需轉載，請註明出處，否則將追究法律責任。