Oracle 闪回特性(Flashback Query、Flashback Table)
Oracle 闪回特性(Flashback Query、Flashback Table)
--==================================================
-- Oracle 闪回特性(Flashback Query、Flashback Table)
--==================================================
Oracle 闪回查询是指针对特定的表来查询特定的时间段内的数据变化情况来确定是否将表闪回到某一个特定的时刻以保证数据无讹误存在。
这个特性大大的减少了采用时点恢复所需的工作量以及数据库脱机的时间。 闪回查询通常分为Flashback Query(闪回查询)，
Flashback Table Query(闪回表查询)，Flashback Version Query(闪回版本查询)，Flashback Transaction Query(闪回事务查询)。本文主要讲
述Flashback Query(闪回查询)，Flashback Table Query(闪回表查询)。其余闪回请参考后续文章。
一、Flashback Query(闪回查询)
通常用于检索一条记录的所有版本，倒退单独的事务或者倒退从指定时间以来对特定表的所有变化
Flashback Query的所有形式取决于UNDO表表空间，关于UDNO表空间请参考：Oracle 回滚(ROLLBACK)和撤销(UNDO)
1.闪回查询(Flashback Query)语法
SELECT <column_name_list>
FROM <table_name>
AS OF <SCN> --使用as of scn
[WHERE <filter_conditions>]
[GROUP BY <unaggregated columns>]
[HAVING <group_filter>]
[ORDER BY <column_positions_or_name>]
SELECT <column_name_list>
FROM <table_name>
AS OF <TIMESTAMP> --使用as of timestamp
[WHERE <filter_conditions>]
[GROUP BY <unaggregated columns>]
[HAVING <group_filter>]
[ORDER BY <column_positions_or_name>]
2.演示闪回查询
a.演示使用as of timestamp来进行闪回查询
flasher@ORCL11G> create table tb1 as
2 select empno,ename,job,deptno from scott.emp where 1=0;
flasher@ORCL11G> insert into tb1
2 select empno,ename,job,deptno
3 from scott.emp where empno in(7369,7499,7521,7566);
flasher@ORCL11G> commit;
flasher@ORCL11G> select * from tb1;
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7369 SMITH CLERK 20
7499 ALLEN SALESMAN 30
7521 WARD SALESMAN 30
7566 JONES MANAGER 20
flasher@ORCL11G> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY'
-------------------
2010-10-25 17:26:08
flasher@ORCL11G> delete from tb1 where job='SALESMAN';
flasher@ORCL11G> commit;
flasher@ORCL11G> select * from tb1;
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7369 SMITH CLERK 20
7566 JONES MANAGER 20
flasher@ORCL11G> select * from tb1 as of timestamp
2 to_timestamp('2010-10-25 17:26:08','yyyy-mm-dd hh24:mi:ss');
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7369 SMITH CLERK 20
7499 ALLEN SALESMAN 30
7521 WARD SALESMAN 30
7566 JONES MANAGER 20
flasher@ORCL11G> select * from tb1 as of timestamp
2 to_timestamp('2010-10-25 17:26:08','yyyy-mm-dd hh24:mi:ss')
3 minus select * from tb1;
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7499 ALLEN SALESMAN 30
7521 WARD SALESMAN 30
b.演示使用as of scn来进行闪回查询
flasher@ORCL11G> select current_scn from v$database;
CURRENT_SCN
-----------
2032782
flasher@ORCL11G> select * from tb1;
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7369 Henry CLERK 20
7566 JONES MANAGER 20
flasher@ORCL11G> delete from tb1 where empno=7369;
flasher@ORCL11G> commit;
flasher@ORCL11G> select * from tb1 as of scn 2032782;
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7369 Henry CLERK 20
7566 JONES MANAGER 20
由以上可知，通过闪回查询获得所需的记录信息，然后来构造新的DML语句并实施其操作来保证数据的完整性。
|
评论暂时关闭