FLASHBACK 闪回查询

create table peng_tt_sms_mt_em as select * from mover.tt_sms_mt_em;

select count(*) from peng_tt_sms_mt_em t

9506

select count(*) from peng_tt_sms_mt_em t
where sms_mt_sn like '137%';
858

SQL> delete peng_tt_sms_mt_em
2 where sms_mt_sn like '137%';

858 rows deleted

SQL> commit;

Commit complete

SQL> select count(*) from peng_tt_sms_mt_em;

COUNT(*)
----------
8648

查找数据库SCN变化
select name,first_change# fscn,next_change# nscn,first_time from v$archived_log
........................
/oracle/product/database/dbs/archarch31895.arc 583416721 583424434 2010-10-26 18:17:36
/oracle/product/database/dbs/archarch31896.arc 583424434 583438531 2010-10-26 21:14:02
/oracle/product/database/dbs/archarch31897.arc 583438531 583440848 2010-10-26 23:30:05
/oracle/product/database/dbs/archarch31899.arc 583441856 583441922 2010-10-26 23:32:07
/oracle/product/database/dbs/archarch31900.arc 583441922 583442001 2010-10-26 23:32:09
/oracle/product/database/dbs/archarch31901.arc 583442001 583442075 2010-10-26 23:32:13
/oracle/product/database/dbs/archarch31898.arc 583440848 583441856 2010-10-26 23:30:55
/oracle/product/database/dbs/archarch31902.arc 583442075 583442970 2010-10-26 23:32:17
/oracle/product/database/dbs/archarch31903.arc 583442970 583474875 2010-10-26 23:57:49
/oracle/product/database/dbs/archarch31904.arc 583474875 583475559 2010-10-27 10:49:37
/oracle/product/database/dbs/archarch31905.arc 583475559 583476298 2010-10-27 10:51:09
/oracle/product/database/dbs/archarch31906.arc 583476298 583477881 2010-10-27 10:53:30
/oracle/product/database/dbs/archarch31907.arc 583477881 583513035 2010-10-27 11:06:46
/oracle/product/database/dbs/archarch31908.arc 583513035 583523166 2010-10-27 15:30:43
/oracle/product/database/dbs/archarch31909.arc 583523166 583534869 2010-10-27 15:35:10
/oracle/product/database/dbs/archarch31910.arc 583534869 583538439 2010-10-27 16:05:06
/oracle/product/database/dbs/archarch31912.arc 583573289 583573349 2010-10-27 23:30:50
/oracle/product/database/dbs/archarch31913.arc 583573349 583573417 2010-10-27 23:30:52
/oracle/product/database/dbs/archarch31914.arc 583573417 583573482 2010-10-27 23:30:57
/oracle/product/database/dbs/archarch31911.arc 583538439 583573289 2010-10-27 16:38:31
/oracle/product/database/dbs/archarch31915.arc 583573482 583573680 2010-10-27 23:31:01
/oracle/product/database/dbs/archarch31916.arc 583573680 583600402 2010-10-27 23:31:45
/oracle/product/database/dbs/archarch31917.arc 583600402 583607273 2010-10-28 10:08:37

查找并获得当前SCN
SQL> select dbms_flashback.get_system_change_number fscn from dual;

FSCN
----------
583600575

create table peng_tt_sms_mt_em_recov as select * from peng_tt_sms_mt_em where 1=0;
SQL> create table peng_tt_sms_mt_em_recov as select * from peng_tt_sms_mt_em where 1=0;

Table created

SQL> select count(*) from peng_tt_sms_mt_em_recov;

COUNT(*)
----------
0
select count(*) from peng_tt_sms_mt_em as of scn 583573680

SQL> select count(*) from peng_tt_sms_mt_em as of scn 583573680;

尝试SCN查询

select count(*) from peng_tt_sms_mt_em as of scn &scn;

enter value for scn:

sql>/ (可以再次输入SCN的值)

select count(*) from peng_tt_sms_mt_em as of scn 583573680

ORA-01466: 无法读数据 - 表定义已更改

select count(*) from peng_tt_sms_mt_em as of scn 583607273;

SQL> select count(*) from peng_tt_sms_mt_em as of scn 583607273;

COUNT(*)
----------
8648

SQL> select count(*) from peng_tt_sms_mt_em as of scn 583607273

SQL> insert into peng_tt_sms_mt_em_recov select * from peng_tt_sms_mt_em as of scn 583607273;

8648 rows inserted

最终只有8648条数据，这和删除后的数据条数一样多。也就是没有回复被删除的数据。我的猜测是时间太久，UNDO被覆盖了。我是在10月28 9点左右建表，插入数据。半个小时后就删除了数据。在10点多在获得一个SCN .用这个SCN：583607273，表还是8648条。这就是我的疑问？？？
但是在获得SCN的时候一直是27号的。在我尝试了很多SCN，只有两种情况，要么是8648，要么是无法读数据-表定义已更改。

如果有人能替我解决这个问题，希望联系我：qq 149196034

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22934571/viewspace-1040533/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22934571/viewspace-1040533/