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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21251711/viewspace-1165012/