1、闪回查询(Flashback Query)
1、闪回查询时间窗口大小依赖UNDO的数据,主要关注以下4点:
1)、SQL> show parameter undo_retention;
NAME TYPE VALUE -------------- ----------- ------------ undo_retention integer 900 |
2)、SQL> select autoextensible from dba_data_files where tablespace_name = (select value from v$parameter where name = 'undo_tablespace');
AUT --- YES |
3)、SQL> select retention from dba_tablespaces where tablespace_name = 'UNDOTBS1';
RETENTION ----------- NOGUARANTEE |
4)、业务繁忙情况 |
2、闪回时间点查询:(4种方法)
1、时间点 select * from hr.employees as of timestamp to_timestamp('2015-05-14 12:40:12', 'YYYY-MM-DD HH24:MI:SS');
|
2、时间间隔 select * from hr.employees as of timestamp(systimestamp - interval '5' minute) where employee_id = 500; --hour、month
|
3、scn select job_id from hr.employees as of scn 944700 where employee_id = 100;
|
4、会话模式 1)将闪回会话定格在15分钟前: SQL> exec dbms_flashback.enable_at_time(systimestamp - interval '15' minute); 2)不再需要“as of”,直接查询15分钟前的数据: SQL> select * from hr.employees 3)关闭闪回会话模式: SQL> exec dbms_flashback.disable;
注意:处于闪回会话模式时,执行dml和ddl将报错; SYS用户不能进入闪回会话模式。 |
3、闪回版本查询:
SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, employee_id, salary from hr.employees versions between timestamp(systimestamp - interval '30' minute) and maxvalue order by 2 nulls first; |
|
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27126919/viewspace-1691748/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27126919/viewspace-1691748/