(f)--闪回恢复区---实践3---闪回查询(基于AUM (auto undo managemet))
flashback query
闪查的数据来自于回滚段
查询结果基于时间和版本
闪查的限制
9I 限制5分钟内
从oracle 9iR1开始， oracle采用了 AUM (auto undo managemet)
9i 版的flashback query由于机制的限制，也存在一些问题和缺点，
如采用时间点的 flashback query，则不能恢复最近5分钟之内的数据，
也就是说，如果一个数据写入不到 5分钟，就被更新或者是删除了，
这个数据在 9i用flashback query 是查询不出来的
原因是由于时间和 Scn的同步每5 分钟的同步一次 smon_scn_time 只存1440 条记录
9I&10G 都受限undo表空间的大小 ,
undo 表空间的大小 ,直接影响到flashback query 的查询能力
如果空间太小 ,闪查的维持时间就短,同时太小会有 ORA-01555
undo_retention=n （秒)参数就是数据维持在 ROLLBACK_segment的不强制保持时间
因为要做 DML 就要先申请rollback 空间 无法申请就无法继续做下去
为了保持新事务正常运行 oracle默认采用了undo_retention 不强制保持时间
就是即使时间不到 ,因空间不足也可以备覆盖重用
10G 后可以将这个参数时间变成强制 但要保证足够大的UNDO
SQL> alter tablepsace undotbs1 retention guarantee;
这样就是不到时间不覆盖
只针对DML 不闪查 DDL操作
闪查种类可分为
1. 闪回SCN或时间查询
2. 闪回事务查询
3. 闪回版本查询
-----------------------------闪查和闪回scn/timestamp----在“(f)--闪回恢复区---实践2---闪回表（闪回DML部分数据会用到闪回查询）”已经介绍了
-----------------------------闪回事务查询 ---------------------------
transaction query 闪事务查询
Flashback的事务查询是通过查询flashback_transaction_query视图来实现的
通过查询该视图能够获得一些事务执行时的信息，甚至包括UNDO语句。
每个事务都有事务ID 以及SCN关联关系
SQL> select ename,sal from scott.emp where deptno=10;
ENAME SAL
---------- ----------
CLARK 2450
KING 5000
MILLER 1300
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
661000
SQL> update scott.emp set sal=sal+1 where deptno=10;
3 rows updated.
SQL> update scott.emp set sal=sal+1 where deptno=10;
3 rows updated.
SQL> commit;
Commit complete.
SQL> update scott.emp set sal=sal+0.1 where deptno=10;
3 rows updated.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
661022
SQL> sal+1 的两次更新在一个事务中 sal+0.1独立一个事务
SQL> set pages 9999
SQL> select xid,COMMIT_SCN,COMMIT_TIMESTAMP,OPERATION,UNDO_SQL from flashback_transaction_query where TABLE_NAME='EMP' and COMMIT_SCN >= 661000 and COMMIT_SCN <= 661022;
XID COMMIT_SCN COMMIT_TI OPERATION UNDO_SQL
---------------- ---------- --------- -------------------------------- -------------------------------
0300170020010000 661020 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '1302' where ROWID = 'AAAMjOAAEAAAAAcAAN';
0300170020010000 661020 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '5002' where ROWID = 'AAAMjOAAEAAAAAcAAI';
0300170020010000 661020 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '2452' where ROWID = 'AAAMjOAAEAAAAAcAAG';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '2451' where ROWID = 'AAAMjOAAEAAAAAcAAG';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '5001' where ROWID = 'AAAMjOAAEAAAAAcAAI';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '1301' where ROWID = 'AAAMjOAAEAAAAAcAAN';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '1300' where ROWID = 'AAAMjOAAEAAAAAcAAN';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMjOAAEAAAAAcAAI';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '2450' where ROWID = 'AAAMjOAAEAAAAAcAAG';
9 rows selected.
SQL> 看事务ID来区分事务
通过查询该视图能够获得一些事务执行时的信息，甚至包括UNDO语句。
每个事务都有事务ID 以及SCN关联关系
SQL> select ename,sal from scott.emp where deptno=10;
ENAME SAL
---------- ----------
CLARK 2450
KING 5000
MILLER 1300
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
661000
SQL> update scott.emp set sal=sal+1 where deptno=10;
3 rows updated.
SQL> update scott.emp set sal=sal+1 where deptno=10;
3 rows updated.
SQL> commit;
Commit complete.
SQL> update scott.emp set sal=sal+0.1 where deptno=10;
3 rows updated.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
661022
SQL> sal+1 的两次更新在一个事务中 sal+0.1独立一个事务
SQL> set pages 9999
SQL> select xid,COMMIT_SCN,COMMIT_TIMESTAMP,OPERATION,UNDO_SQL from flashback_transaction_query where TABLE_NAME='EMP' and COMMIT_SCN >= 661000 and COMMIT_SCN <= 661022;
XID COMMIT_SCN COMMIT_TI OPERATION UNDO_SQL
---------------- ---------- --------- -------------------------------- -------------------------------
0300170020010000 661020 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '1302' where ROWID = 'AAAMjOAAEAAAAAcAAN';
0300170020010000 661020 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '5002' where ROWID = 'AAAMjOAAEAAAAAcAAI';
0300170020010000 661020 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '2452' where ROWID = 'AAAMjOAAEAAAAAcAAG';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '2451' where ROWID = 'AAAMjOAAEAAAAAcAAG';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '5001' where ROWID = 'AAAMjOAAEAAAAAcAAI';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '1301' where ROWID = 'AAAMjOAAEAAAAAcAAN';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '1300' where ROWID = 'AAAMjOAAEAAAAAcAAN';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMjOAAEAAAAAcAAI';
04000D00E6000000 661014 27-FEB-11 UPDATE update "SCOTT"."EMP" set "SAL" = '2450' where ROWID = 'AAAMjOAAEAAAAAcAAG';
9 rows selected.
SQL> 看事务ID来区分事务
-----------------------------闪回版本查询 ---------------------------
闪版版本查询使用伪列 获取一段时间内的版本
伪列:versions_starttime、versions_endtime、versions_xid、versions_operation
versions_startscn version_starttime
操作时的SCN和时间 如果为空 表示该行在查询范围之外创建
versions_endscn versions_endtime
失效时的SCN和时间 如果为空 表示该行被删除或在查询范围内无改动
versions_xid
事务ID
versions_operation
该行被执行的操作 I(insert) D(delete) U(update)
minvalue maxvalue
版本的最大时间值和最小时间值
SQL> !date "+%F %T"
2011-02-27 00:15:05
SQL>
col versions_starttime for a20
col versions_endtime for a20
SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation,ename,sal
from scott.emp versions between timestamp to_timestamp('2011-02-27 00:15:05', 'YYYY-MM-DD HH24:MI:SS')
and maxvalue order by VERSIONS_STARTTIME ;
VERSIONS_S VERSIONS_E VERSIONS_XID V ENAME SAL
---------- ---------- ---------------- - ---------- ----------
SCOTT 3003
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
FORD 3000
BLAKE 2850
TURNER 1500
ADAMS 1100
JAMES 950
MARTIN 1250
11 rows selected.
SQL> update scott.emp set sal=sal+1;
11 rows updated.
SQL> commit;
Commit complete.
SQL> update scott.emp set sal=sal+1;
11 rows updated.
SQL> commit;
Commit complete.
SQL> delete scott.emp where deptno=30;
6 rows deleted.
SQL> commit;
Commit complete.
SQL>
SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation,ename,sal
from scott.emp versions between timestamp to_timestamp('2011-02-27 00:15:05', 'YYYY-MM-DD HH24:MI:SS')
and maxvalue order by VERSIONS_STARTTIME ;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V ENAME SAL
-------------------- -------------------- ---------------- - ---------- ----------
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U SCOTT 3004
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U SMITH 801
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U ALLEN 1601
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U BLAKE 2851
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U MARTIN 1251
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U JONES 2976
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U WARD 1251
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U TURNER 1501
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U ADAMS 1101
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U JAMES 951
2011-02-27 00:15:43. 2011-02-27 00:15:46. 0A001000E6000000 U FORD 3001
2011-02-27 00:15:46. 07002100F5000000 U JONES 2977
2011-02-27 00:15:46. 07002100F5000000 U FORD 3002
2011-02-27 00:15:46. 2011-02-27 00:15:58. 07002100F5000000 U WARD 1252
2011-02-27 00:15:46. 2011-02-27 00:15:58. 07002100F5000000 U JAMES 952
2011-02-27 00:15:46. 07002100F5000000 U ADAMS 1102
2011-02-27 00:15:46. 07002100F5000000 U SCOTT 3005
2011-02-27 00:15:46. 2011-02-27 00:15:58. 07002100F5000000 U TURNER 1502
2011-02-27 00:15:46. 2011-02-27 00:15:58. 07002100F5000000 U BLAKE 2852
2011-02-27 00:15:46. 07002100F5000000 U SMITH 802
2011-02-27 00:15:46. 2011-02-27 00:15:58. 07002100F5000000 U ALLEN 1602
2011-02-27 00:15:46. 2011-02-27 00:15:58. 07002100F5000000 U MARTIN 1252
2011-02-27 00:15:58. 01001600E4000000 D ALLEN 1602
2011-02-27 00:15:58. 01001600E4000000 D WARD 1252
2011-02-27 00:15:58. 01001600E4000000 D MARTIN 1252
2011-02-27 00:15:58. 01001600E4000000 D BLAKE 2852
2011-02-27 00:15:58. 01001600E4000000 D TURNER 1502
2011-02-27 00:15:58. 01001600E4000000 D JAMES 952
2011-02-27 00:15:43. FORD 3000
2011-02-27 00:15:43. SCOTT 3003
2011-02-27 00:15:43. ADAMS 1100
2011-02-27 00:15:43. TURNER 1500
2011-02-27 00:15:43. BLAKE 2850
2011-02-27 00:15:43. JONES 2975
2011-02-27 00:15:43. WARD 1250
2011-02-27 00:15:43. ALLEN 1600
2011-02-27 00:15:43. SMITH 800
2011-02-27 00:15:43. JAMES 950
2011-02-27 00:15:43. MARTIN 1250
39 rows selected.
SQL>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28602568/viewspace-758924/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- Oracle 跟踪/告警/监听日志的清理脚本
- Oracle JAVA变量类型与字段类型不一致，分区表全扫(INTERNAL_FUNCTION隐含转换)的优化
- EXPDP dumpfile和parallel的关系
- Oracle not exist子查询全扫的优化
- Oracle 不知用户的密码时,如何获取dblink创建语句
- Oracle 快速将数据导出到CSV(Excel)文件的方法介绍
- Redis 基本搭建
- PLSQL Developer Profiler 使用 【存储过程调优 工具推荐】
- Oracle 针对子查询里有group by 表全扫的优化
- Oracle 使用sqlnet.ora/trigger限制/允许某IP或IP段访问指定用户
