flashback drop/query/table/database/archive
flashback分類：
flashback drop：利用recyclebin；只要被drop的表不在system表空間，就可利用flashback drop來恢復，若在system表空間的表，則只能做不完全恢復。
例：
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
56
SQL> drop table scott.emp1;
Table dropped.
SQL> conn scott/tiger
Connected.
SQL> select * from emp1;
select * from emp1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP1 BIN$9rtypb7+eLjgQAB/AQAZWg==$0 TABLE 2014-04-11:09:42:34
SQL> select * from "BIN$9rtypb7+eLjgQAB/AQAZWg==$0";
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
56 rows selected.
SQL> flashback table emp1 to before drop;
Flashback complete.
注意：若恢復之後要改名，可改為flashback table emp1 to before drop rename to newname。
SQL> select count(1) from emp1;
COUNT(1)
----------
56
SQL> show recyclebin;
注意：閃回之後，回收站資料清空了。
flashback query：利用undo資料做閃回
SQL> set time on
10:24:00 SQL> select count(1) from scott.emp1;
COUNT(1)
----------
56
10:24:41 SQL> delete from scott.emp1;
56 rows deleted.
10:24:49 SQL> commit;
Commit complete.
10:24:51 SQL> insert into scott.emp1 select * from scott.emp where rownum<=4;
4 rows created.
10:25:10 SQL> commit;
Commit complete.
10:25:12 SQL> select sysdate from dual;
SYSDATE
---------
11-APR-14
10:25:52 SQL> select * from scott.emp1 as of timestamp to_timestamp('2014-04-11 10:24:41','yyyy-mm-dd hh24:mi:ss');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
56 rows selected.
注意：flashback query時間點可以通過logminer工具來獲取，如果誤操作時間可估算，可以先估一個時間點，然後看查詢結果是否正確，若結果就是要恢復的資料就可利用此時間點。
另外，假如以上誤操作後，我只要恢復之前存在的56條記錄，新增的3條記錄也要刪除，此時千萬要記住，truncate table之前，一定要先將flashback query的資料提到臨時備份表中，然後再做truncate操作，因為flashback query是通過undo資訊獲取的，ddl操作會做提交，undo資訊會被覆蓋。
10:27:01 SQL> create table scott.emp1_bak as select * from scott.emp1 as of timestamp to_timestamp('2014-04-11 10:24:41','yyyy-mm-dd hh24:mi:ss');
Table created.
10:27:51 SQL> select count(1) from scott.emp1_bak;
COUNT(1)
----------
56
--truncate table之後，再做flashback query會報錯，如下示：
10:28:02 SQL> truncate table scott.emp1;
Table truncated.
10:28:34 SQL> select * from scott.emp1 as of timestamp to_timestamp('2014-04-11 10:24:41','yyyy-mm-dd hh24:mi:ss');
select * from scott.emp1 as of timestamp to_timestamp('2014-04-11 10:24:41','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
10:28:43 SQL> select * from scott.emp1;
no rows selected
10:29:13 SQL> insert into scott.emp1 select * from scott.emp1_bak;
56 rows created.
10:29:32 SQL> select count(1) from scott.emp1;
COUNT(1)
----------
56
10:29:39 SQL> drop table scott.emp1_bak;
Table dropped.
11:23:23 SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP1_BAK BIN$9rwbekrTaCTgQAB/AQAacA==$0 TABLE 2014-04-11:10:29:47
11:30:30 SQL> purge recyclebin;
Recyclebin purged.
flashback table(scn)：利用undo資料做閃回
SQL> conn / as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
522827
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
56
SQL> delete from scott.emp1;
56 rows deleted.
SQL> commit;
Commit complete.
SQL> insert into scott.emp1 select * from scott.emp1 where rownum<4;
0 rows created.
SQL> commit;
Commit complete.
SQL> flashback table scott.emp1 to scn 522827 ;
flashback table scott.emp1 to scn 522827
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
SQL> alter table scott.emp1 enable row movement;
Table altered.
SQL> flashback table scott.emp1 to scn 522827 ;
Flashback complete.
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
56
注意：flashback table的時候，要知道scn號，可通過logminer來獲取，另外表的row movement要設定為enable。flashback table是直接恢復到表原來的狀態，若要保留誤操作之後新增的資料，則可使用前面的flashback query方式進行恢復。
flashback database：利用閃回日誌，閃回時間必須為誤操作時間減一秒
--開啟logminer
SQL> alter database add supplemental log data;
Database altered.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
YES NO NO
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
56
SQL> truncate table scott.emp1;
Table truncated.
SQL> insert into scott.emp1 select * from scott.emp where rownum<=4;
4 rows created.
SQL> commit;
Commit complete.
--檢視日誌與歸檔資訊
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
FIRST_TIM NEXT_CHANGE# NEXT_TIME
--------- ------------ ---------
1 1 1 104857600 512 2 YES INACTIVE 471036
10-APR-14 494190 11-APR-14
2 1 2 104857600 512 2 YES INACTIVE 494190
11-APR-14 516214 11-APR-14
3 1 3 104857600 512 2 NO CURRENT 516214
11-APR-14 2.8147E+14
SQL> col member for a50;
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo01.log NO
1 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo01.log NO
2 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo02.log NO
2 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo02.log NO
3 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo03.log NO
3 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo03.log NO
6 rows selected.
SQL> select name from v$archived_log;
NAME
----------------------------------------------------------------------------------------------------
/home/oracle/PROD/1_16_827176743.dbf
/home/oracle/PROD/1_17_827176743.dbf
/home/oracle/PROD/1_18_827176743.dbf
/home/oracle/PROD/1_19_827176743.dbf
/home/oracle/PROD/1_20_827176743.dbf
/home/oracle/PROD/1_21_827176743.dbf
/home/oracle/PROD/1_22_827176743.dbf
/home/oracle/PROD/1_23_827176743.dbf
/home/oracle/PROD/1_24_827176743.dbf
/home/oracle/PROD/1_25_827176743.dbf
/home/oracle/PROD/1_26_827176743.dbf
NAME
----------------------------------------------------------------------------------------------------
/home/oracle/PROD/1_27_827176743.dbf
/home/oracle/PROD/1_28_827176743.dbf
/home/oracle/PROD/1_1_844469007.dbf
/home/oracle/PROD/1_2_844469007.dbf
/home/oracle/PROD/1_1_844532679.dbf
/home/oracle/PROD/1_2_844532679.dbf
/home/oracle/PROD/1_4_844532679.dbf
/home/oracle/PROD/1_2_844532679.dbf
/home/oracle/PROD/1_3_844532679.dbf
/home/oracle/PROD/1_1_844537684.dbf
/home/oracle/PROD/1_2_844537684.dbf
22 rows selected.
--使用logmnr查詢時間點
SQL> execute dbms_LOGMNR.ADD_LOGFILE( -
> LOGFILENAME => '/u01/app/oracle/oradata/PROD/disk1/redo03.log', -
> OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
> LOGFILENAME => '/home/oracle/PROD/1_2_844537684.dbf', -
> OPTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => -
> DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> col username for a10;
SQL> col sql_redo for a100;
SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where sql_redo like '%truncate%' order by scn;
USERNAME SCN TIMESTAMP
---------- ---------- -------------------
SQL_REDO
----------------------------------------------------------------------------------------------------
SCOTT 521634 2014-04-11 10:28:29
truncate table scott.emp1;
SYS 523397 2014-04-11 12:04:46
truncate table scott.emp1;
SQL> exec dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
注意：因為這次誤操作是truncate，不是dml操作，會有undo資訊，所以無法使用前面的flashback query及flashback table方式進行閃回恢復，但可使用flashback database方式，如下：
--利用logmnr查詢的時間點，執行flashback database（分三步：正常關庫、啟庫到nomount、flashback database to timestamp）
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
--先嚐試恢復至誤操作的時間點
SQL> flashback database to timestamp to_timestamp('2014-04-11 12:04:46','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
--啟庫至read only狀態，先檢視下恢復後的記錄數（為0，驗證了前面所說的，flashback database必須減一秒）
SQL> alter database open read only;
Database altered.
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
0
--再次做flashback database（閃回至誤操作時間上一秒）
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('2014-04-11 12:04:45','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
56
注意：這次閃回成功了，關庫後，真正做閃回，並用resetlogs方式開庫
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('2014-04-11 12:04:45','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
56
注意：flashback database只要開庫時未使用resetlogs方式，就可執行多次flashback database。每次執行完，先用open read only的方式開庫，驗證下閃回後的結果是否正確，若正確就可以用resetlogs方式進行開庫了。另外，flashback database的方式，閃回時間點需要減一秒。
flashback archive
flashback archive只能在ASSM的tablespace上建立，只保護重要的表。
SQL> col name for a50;
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf
5 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
--建立表空間fba
SQL> create tablespace fba datafile '/u01/app/oracle/oradata/PROD/disk3/fba01.dbf' size 50m
2 autoextend on next 10m maxsize 200m;
Tablespace created.
--在表空間fba上建立閃迴歸檔fba1
SQL> create flashback archive fba1 tablespace fba retention 1 year;
Flashback archive created.
--將閃迴歸檔的許可權賦予給scott使用者
SQL> grant flashback archive on fba1 to scott;
Grant succeeded.
SQL> conn scott/tiger
Connected.
--將emp表用閃迴歸檔保護起來
SQL> alter table emp flashback archive fba1;
Table altered.
SQL> conn / as sysdba
Connected.
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> truncate table scott.emp;
Table truncated.
SQL> set time on
06:54:41 SQL> select sysdate from dual;
SYSDATE
---------
12-APR-14
06:54:49 SQL> select * from scott.emp;
no rows selected
注意：truncate的時候忘記開啟時間了，truncate之後再開啟時間，這裡估算truncate操作時間大概為2014-04-12 06:53:01（因為emp表設定了閃迴歸檔，所以truncate時會非常慢），一會用來flashback archive。
06:59:07 SQL> select * from scott.emp as of timestamp to_timestamp('2014-04-12 06:53:01','yyyy-mm-dd hh24:mi:ss');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7369 SMITH CLERK 7902 17-DEC-80 800 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7839 KING PRESIDENT 17-NOV-81 5000 10
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
14 rows selected.
注意：這裡雖然是DDl操作，但也可以通過flash query查詢到資料，實際上它不是利用undo資訊（DDl不會產生undo），而是利用了閃迴歸檔資訊。
--恢復emp表資料
06:59:38 SQL> insert into scott.emp select * from scott.emp as of timestamp to_timestamp('2014-04-12 06:53:01','yyyy-mm-dd hh24:mi:ss');
14 rows created.
07:04:02 SQL> select count(1) from scott.emp;
COUNT(1)
----------
14
07:04:12 SQL> commit;
Commit complete
--查詢scott使用者下的閃迴歸檔資訊
SQL> conn scott/tiger
Connected.
SQL> col TABLE_NAME for a10;
SQL> col OWNER_NAME for a10;
SQL> col FLASHBACK_ARCHIVE_NAME for a30;
SQL> col ARCHIVE_TABLE_NAME for a30;
SQL> select * from user_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
---------- ---------- ------------------------------ ------------------------------ --------
EMP SCOTT FBA1 SYS_FBA_HIST_13014 ENABLED
注意：查詢user_flashback_archive_tables表只能用scott使用者查詢。從查詢結果可知，之前對emp表做truncate操作之前，實際生成了一個對應的小表SYS_FBA_HIST_13014，用來存放emp表資料，這個表可以儲存一年（設定了retention 1 year）。
下面我們來查詢下SYS_FBA_HIST_13014表的資訊
SQL> col rid for a20;
SQL> set linesize 200;
SQL> select * from SYS_FBA_HIST_13014;
RID STARTSCN ENDSCN XID O EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------------------- ---------- ---------- ---------------- - ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
AAADLWAAEAAAACRAAF 551719 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
AAADLWAAEAAAACRAAN 551719 7934 MILLER CLERK 7782 23-JAN-82 1300 10
AAADLWAAEAAAACRAAD 551719 7566 JONES MANAGER 7839 02-APR-81 2975 20
AAADLWAAEAAAACRAAM 551719 7902 FORD ANALYST 7566 03-DEC-81 3000 20
AAADLWAAEAAAACRAAC 551719 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
AAADLWAAEAAAACRAAJ 551719 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
AAADLWAAEAAAACRAAA 551719 7369 SMITH CLERK 7902 17-DEC-80 800 20
AAADLWAAEAAAACRAAH 551719 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
AAADLWAAEAAAACRAAB 551719 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
AAADLWAAEAAAACRAAI 551719 7839 KING PRESIDENT 17-NOV-81 5000 10
AAADLWAAEAAAACRAAE 551719 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
RID STARTSCN ENDSCN XID O EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------------------- ---------- ---------- ---------------- - ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
AAADLWAAEAAAACRAAL 551719 7900 JAMES CLERK 7698 03-DEC-81 950 30
AAADLWAAEAAAACRAAG 551719 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
AAADLWAAEAAAACRAAK 551719 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
14 rows selected.
--與emp表結構對照下
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7369 SMITH CLERK 7902 17-DEC-80 800 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7839 KING PRESIDENT 17-NOV-81 5000 10
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
14 rows selected.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1165012/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Flashback database與flashback table使用條件區別Database
- Oracle OCP 1Z0 053 Q138(DROP FLASHBACK ARCHIVE)OracleHive
- Query to Check SP/Table/Trigger Exists in Database or notDatabase
- MySQL 5.6 drop database時,table metadata lock等待MySqlDatabase
- flashback技術之---flashback drop
- 【FLASHBACK】Oracle flashback data archive 介紹OracleHive
- 【Flashback】Flashback Query功能實踐
- flashback技術之---flashback query
- Oracle Flashback Data ArchiveOracleHive
- Flashback Data Archive RequirementsHiveUIREM
- flashback version query和 flashback transaction query簡單應用
- Drop DatabaseDatabase
- 閃回之 回收站、Flashback Drop （table、index、trigger等）Index
- flashback技術之---flashback Transaction Query
- flashback技術之---flashback version query
- flashback總結三之Flashback_DROP
- Flashback Query Benefits (368)
- Flashback DatabaseDatabase
- Oracle10g 回收站及徹底刪除table : drop table xx purge 以及flashbackOracle
- oracle 10g flashback version query 和 flashback transaction query實驗Oracle 10g
- flashback總結四之Flashback_Query
- 【Flashback】Flashback Table功能實踐
- flashback技術之---flashback table
- oracle10g之flashback version query 和flashback transaction query 實驗Oracle
- ORACLE Flashback Query偽列Oracle
- flashback技術之---flashback databaseDatabase
- Flashback Data Archive原理詳解Hive
- Flashback Data Archive (Oracle Total Recall)HiveOracle
- 【Flashback】使用Flashback Drop技術閃回被DROP表的指定版本資料
- flashback總結六之Flashback_Transaction_Query
- 【實驗】【Flashback】Flashback Transaction Query功能實踐
- flashback database 方法Database
- 配置Flashback DatabaseDatabase
- 【Flashback】Flashback Drop閃回刪除功能實踐
- 閃回之 Flashback Query （dml表、過程、函式、包等）、Flashback version Query函式
- flashback系列文章三（flashback database）Database
- Oracle10g的Flashback之Flashback Transaction QueryOracle
- flashback總結五之Flashback_Query_Version(上)