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/