当前数据库有undotbs1和undotbs2两个撤销表空间,和一个空表flash_t,进行如下操作:
1.将当前的undo表空间设置为undotbs2,向flash_t中插入数据并提交:
SYS@orcl
13-OCT-14>show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
HH@orcl
13-OCT-14>insert into flash_t select * from dba_users;
44 rows created.
HH@orcl 13-OCT-14>select count(1) from flash_T;
COUNT(1)
----------
44
44 rows created.
HH@orcl 13-OCT-14>select count(1) from flash_T;
COUNT(1)
----------
44
HH@orcl
13-OCT-14>commit;
2.查询当前的系统时间:
HH@orcl
13-OCT-14>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2014-10-13 00:33:11
TO_CHAR(SYSDATE,'YY
-------------------
2014-10-13 00:33:11
3.删除flash_t中的所有数据,并提交:
4.切换到undotbs1:
5.删除undotbs2:
6.尝试将表flash_t闪回到以上记录的时间点:
HH@orcl
13-OCT-14>flashback table flash_t to timestamp to_timestamp('2014-10-13 00:33:11','yyyy-mm-dd hh24:mi:ss');
flashback table flash_t to timestamp to_timestamp('2014-10-13 00:33:11','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
HH@orcl 13-OCT-14>alter table flash_t enable row movement;
Table altered.
HH@orcl 13-OCT-14>flashback table flash_t to timestamp to_timestamp('2014-10-13 00:33:11','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
flashback table flash_t to timestamp to_timestamp('2014-10-13 00:33:11','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
HH@orcl 13-OCT-14>alter table flash_t enable row movement;
Table altered.
HH@orcl 13-OCT-14>flashback table flash_t to timestamp to_timestamp('2014-10-13 00:33:11','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
我们知道,闪回查询,闪回事务和闪回表这三种闪回技术都是通过undo segment实现的,前面,我们刚开始使用的是undotbs2,在这期间向表中插入了数据,那么这些数据理应在undotbs2中有相应的快照。之后,切换到了undotbs1,并且把存在flash_t数据的undotbs2删除了,那么当我们使用闪回表的时候,就无法找到undotbs2上的数据来进行闪回了,但是我们仍然闪回成功了,这是为什么呢?
答案就是这些数据还存在buffer里面,oracle首先会到buffer去查看,如果buffer里面没有,才会到undotbs2中查看并返回结果。
这是我们业务不繁忙的测试环境,如果在一个繁忙的生产系统上的话,是很大可能或者说基本上是无法闪回成功的,因为在繁忙的系统上,buffer中存在的数据会被冲刷掉,而undotbs2又被删除了。
下面我们重复上面的步骤,但是在闪回之前把buffer清空,看看会发生什么:
当前undo表空间是undotbs2:
SYS@orcl
13-OCT-14>show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
向表中插入数据,提交:
HH@orcl
13-OCT-14>commit;
Commit complete.
HH@orcl 13-OCT-14>select count(*) from flash_T;
COUNT(*)
----------
44
Commit complete.
HH@orcl 13-OCT-14>select count(*) from flash_T;
COUNT(*)
----------
44
查询当前系统时间:
HH@orcl
13-OCT-14>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2014-10-13 01:15:11
TO_CHAR(SYSDATE,'YY
-------------------
2014-10-13 01:15:11
删除flash_t的数据且提交:
HH@orcl
13-OCT-14>delete from flash_t;
44 rows deleted.
HH@orcl 13-OCT-14>commit;
Commit complete.
HH@orcl 13-OCT-14>select count(*) from flash_T;
COUNT(*)
----------
0
44 rows deleted.
HH@orcl 13-OCT-14>commit;
Commit complete.
HH@orcl 13-OCT-14>select count(*) from flash_T;
COUNT(*)
----------
0
切换到undotbs1,且删除undotbs2表空间:
SYS@orcl
13-OCT-14>alter system set undo_tablespace=undotbs1;
System altered.
SYS@orcl 13-OCT-14>drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped.
System altered.
SYS@orcl 13-OCT-14>drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped.
把buffer cache清空:
尝试将表闪回到以上记录的时间点:
HH@orcl
13-OCT-14>alter table flash_t enable row movement;
Table altered.
HH@orcl 13-OCT-14>flashback table flash_t to timestamp to_timestamp('2014-10-13 01:15:11','yyyy-mm-dd hh24:mi:ss');
flashback table flash_t to timestamp to_timestamp('2014-10-13 01:15:11','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-12801: error signaled in parallel query server P000
ORA-01555: snapshot too old: rollback segment number with name "" too small
Table altered.
HH@orcl 13-OCT-14>flashback table flash_t to timestamp to_timestamp('2014-10-13 01:15:11','yyyy-mm-dd hh24:mi:ss');
flashback table flash_t to timestamp to_timestamp('2014-10-13 01:15:11','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-12801: error signaled in parallel query server P000
ORA-01555: snapshot too old: rollback segment number with name "" too small
可以看到,这时会出现一个ORA-01555快照过旧的错误。因为oracle已经找不到指定时间点的快照了,所以报错。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29800581/viewspace-1299206/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29800581/viewspace-1299206/