【求助】请高手们给指点一下,DELETE删除的数据如何恢复原样? 【求助】请高手们给指点一下,DELETE删除的数据如何恢复原样? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 REDO或者闪回,自己搜索下闪回容易点 flashback你搜这个思路:select *from tableas of timestamp to_timestamp('2008-11-11 12:00:00', 'yyyy-mm-dd hh24:mi:ss')--查询这个时间点的该表的数据具体怎么弄去问baidu October 22nd, 2008 | Categories: Boring, ELNINO | Tags: flashback, Oracle, Undo Leave a comment | Trackback 10g的新功能。如果错误的修改了表的数据,且之前没有对受影响的数据进行备份。可以选择flashback table作为表不完全恢复的方法。1)首先,flashback table将基于flashback SCN 或者 time stamp 从 undo tablespace 中读取指定恢复时间戳的原始映像的ROWID和是否改动的情况,并将信息存储在一个临时表 SYS_TEMP_FBT.2)然后,在当前表中删除应该要删除的纪录。这些被删除的纪录是指定恢复时间戳之后update和insert过的数据.3)最后,通过flashback query和临时表SYS_TEMP_FBT,得到指定恢复时间戳那时存在的且在步骤2中被删除的纪录。描述的点复杂。SQL> select * from tab;TNAME TABTYPE CLUSTERID—————————— ——- ———-BINZHANG TABLESQL> !dateTue Oct 21 22:53:57 GMT 2008SQL> begin2 for i in 1..1000 loop3 delete from binzhang where id=i;4 commit;5 end loop;6 end;7 /PL/SQL procedure successfully completed.SQL> select count(*) from binzhang;COUNT(*)———-10727SQL> alter table binzhang ENABLE ROW MOVEMENT;Table altered.SQL> create table a as select * from v$Mystat;Table created.SQL> flashback table binzhang TO TIMESTAMP TO_TIMESTAMP(’2008-10-21 22:53:00′,’yyyy-mm-dd hh24:mi:ss’);Flashback complete.SQL> create table b as select * from v$Mystat;Table created.SQL> select count(*) from binzhang;COUNT(*)———-11727 具体的信息要从trace中来观察。flashback操作完成后,可以发现schema多了一个临时表。SQL> select * from tab;TNAME TABTYPE CLUSTERID—————————— ——- ———-BINZHANG TABLESYS_TEMP_FBT TABLESQL> select count(*) from SYS_TEMP_FBT;COUNT(*)———-0SQL> desc SYS_TEMP_FBTName Null? Type—————————————– ——– —————————-SCHEMA VARCHAR2(32)OBJECT_NAME VARCHAR2(32)OBJECT# NUMBERRID ROWIDACTION CHAR(1)SQL> drop table SYS_TEMP_FBT;Table dropped. 以下是从trace中看到的truncate table SYS_TEMP_FBTINSERT /*+ APPEND */ into SYS_TEMP_FBT SELECT /*+ FBTSCAN FULL(S) PARALLEL(S,DEFAULT) */ :1, :2, :3, rowid, SYS_FBT_INSDEL FROM “XFAN”.”BINZHANG” as of SCN :4 SRows Row Source Operation——- —————————————————1 LOAD AS SELECT (cr=3 pr=0 pw=0 time=0 us)2198 PX COORDINATOR (cr=3 pr=0 pw=0 time=24 us)0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=3 size=20592 card=1716)0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=3 size=20592 card=1716)0 TABLE ACCESS FULL BINZHANG (cr=0 pr=0 pw=0 time=0 us cost=3 size=20592 card=1716)DELETE /*+ BYPASS_UJVC */ FROM (SELECT /*+ ORDERED USE_NL(S) PARALLEL(S,DEFAULT) PARALLEL(T,DEFAULT) */ S.rowid FROM SYS_TEMP_FBT T,”XFAN”.”BINZHANG” S WHERE T.rid = S.rowid and T.action = ‘D’ and T.object# = : 1) VRows Row Source Operation——- —————————————————0 DELETE BINZHANG (cr=2 pr=4 pw=4 time=0 us)599 PX COORDINATOR (cr=2 pr=0 pw=0 time=23 us)0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=3 size=2179177 card=539)0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=3 size=2179177 card=539)0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)0 TABLE ACCESS FULL SYS_TEMP_FBT (cr=0 pr=0 pw=0 time=0 us cost=2 size=24108 card=6)0 TABLE ACCESS BY USER ROWID BINZHANG (cr=0 pr=0 pw=0 time=0 us cost=1 size=2250 card=90)INSERT /*+ PARALLEL(S, DEFAULT) PARALLEL(T, DEFAULT) */ INTO “XFAN”.”BINZHANG” SELECT /*+ USE_NL(S) ORDERED PARALLEL(S, DEFAULT) PARALLEL(T, DEFAULT) */ S.* FROM SYS_TEMP_FBT T , “XFAN”.”BINZHANG” as of SCN :1 S WHERE T.rid = S.rowid and T.action = ‘I’ and T.object# = :2Rows Row Source Operation——- —————————————————0 LOAD TABLE CONVENTIONAL (cr=7 pr=0 pw=0 time=0 us)1599 PX COORDINATOR (cr=2 pr=0 pw=0 time=31 us)0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=4 size=1107782 card=274)0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=4 size=1107782 card=274)0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)0 TABLE ACCESS FULL SYS_TEMP_FBT (cr=0 pr=0 pw=0 time=0 us cost=2 size=64288 card=16)0 TABLE ACCESS BY USER ROWID BINZHANG (cr=0 pr=0 pw=0 time=0 us cost=1 size=425 card=17)Oracle通过在flashback query接口上封装了一下,实现了flashback table的功能。从SQL PLAN中也可以看到,flashback至少需要在表上作一次full table scan,如果表很大而且修改频繁,其IO成本可想而知。还可以研究一下SYS_FBT_INSDEL function and FBTSCAN hint 如果delete后,还没有提交,可以用rollback如果已经提交,就要用闪回,flashback到一个时间点 明天要去面试,是oracle+linux,大家支支招吧,谢了! 请教一个sql语句 求救:如何定时每天晚上21点定时更新2个结构完全相同的ORACLE数据库中的4个表 SQL查询语句 如何求得两个周次的天数 怎样在存储过程中返回多个“临时”的记录集? instr和decode的用法 急急急!求大神!排序 同organ_id、同user_id 的数据 只取一条 这种语语句怎么关联 oracle8.16(或8.17)向低版本建数据链路问题 pl/sql DEVELOPER连接oracle服务器端,都需要那些条件。 oracle 中sid与service_name
或者闪回,自己搜索下
闪回容易点
select *
from table
as of timestamp to_timestamp('2008-11-11 12:00:00', 'yyyy-mm-dd hh24:mi:ss')--查询这个时间点的该表的数据
具体怎么弄去问baidu
—————————— ——- ———-
BINZHANG TABLESQL> !date
Tue Oct 21 22:53:57 GMT 2008SQL> begin
2 for i in 1..1000 loop
3 delete from binzhang where id=i;
4 commit;
5 end loop;
6 end;
7 /PL/SQL procedure successfully completed.SQL> select count(*) from binzhang;
COUNT(*)
———-
10727SQL> alter table binzhang ENABLE ROW MOVEMENT;
Table altered.SQL> create table a as select * from v$Mystat;
Table created.SQL> flashback table binzhang TO TIMESTAMP TO_TIMESTAMP(’2008-10-21 22:53:00′,’yyyy-mm-dd hh24:mi:ss’);
Flashback complete.SQL> create table b as select * from v$Mystat;
Table created.SQL> select count(*) from binzhang;
COUNT(*)
———-
11727 具体的信息要从trace中来观察。flashback操作完成后,可以发现schema多了一个临时表。SQL> select * from tab;
TNAME TABTYPE CLUSTERID
—————————— ——- ———-
BINZHANG TABLE
SYS_TEMP_FBT TABLESQL> select count(*) from SYS_TEMP_FBT;
COUNT(*)
———-
0SQL> desc SYS_TEMP_FBT
Name Null? Type
—————————————– ——– —————————-
SCHEMA VARCHAR2(32)
OBJECT_NAME VARCHAR2(32)
OBJECT# NUMBER
RID ROWID
ACTION CHAR(1)SQL> drop table SYS_TEMP_FBT;
Table dropped. 以下是从trace中看到的truncate table SYS_TEMP_FBTINSERT /*+ APPEND */ into SYS_TEMP_FBT SELECT /*+ FBTSCAN FULL(S) PARALLEL(S,DEFAULT) */ :1, :2, :3, rowid, SYS_FBT_INSDEL FROM “XFAN”.”BINZHANG” as of SCN :4 SRows Row Source Operation
——- —————————————————
1 LOAD AS SELECT (cr=3 pr=0 pw=0 time=0 us)
2198 PX COORDINATOR (cr=3 pr=0 pw=0 time=24 us)
0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=3 size=20592 card=1716)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=3 size=20592 card=1716)
0 TABLE ACCESS FULL BINZHANG (cr=0 pr=0 pw=0 time=0 us cost=3 size=20592 card=1716)DELETE /*+ BYPASS_UJVC */ FROM (SELECT /*+ ORDERED USE_NL(S) PARALLEL(S,DEFAULT) PARALLEL(T,DEFAULT) */ S.rowid FROM SYS_TEMP_FBT T,”XFAN”.”BINZHANG” S WHERE T.rid = S.rowid and T.action = ‘D’ and T.object# = : 1) VRows Row Source Operation
——- —————————————————
0 DELETE BINZHANG (cr=2 pr=4 pw=4 time=0 us)
599 PX COORDINATOR (cr=2 pr=0 pw=0 time=23 us)
0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=3 size=2179177 card=539)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=3 size=2179177 card=539)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL SYS_TEMP_FBT (cr=0 pr=0 pw=0 time=0 us cost=2 size=24108 card=6)
0 TABLE ACCESS BY USER ROWID BINZHANG (cr=0 pr=0 pw=0 time=0 us cost=1 size=2250 card=90)
INSERT /*+ PARALLEL(S, DEFAULT) PARALLEL(T, DEFAULT) */ INTO “XFAN”.”BINZHANG” SELECT /*+ USE_NL(S) ORDERED PARALLEL(S, DEFAULT) PARALLEL(T, DEFAULT) */ S.* FROM SYS_TEMP_FBT T , “XFAN”.”BINZHANG” as of SCN :1 S WHERE T.rid = S.rowid and T.action = ‘I’ and T.object# = :2Rows Row Source Operation
——- —————————————————
0 LOAD TABLE CONVENTIONAL (cr=7 pr=0 pw=0 time=0 us)
1599 PX COORDINATOR (cr=2 pr=0 pw=0 time=31 us)
0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=4 size=1107782 card=274)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=4 size=1107782 card=274)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL SYS_TEMP_FBT (cr=0 pr=0 pw=0 time=0 us cost=2 size=64288 card=16)
0 TABLE ACCESS BY USER ROWID BINZHANG (cr=0 pr=0 pw=0 time=0 us cost=1 size=425 card=17)Oracle通过在flashback query接口上封装了一下,实现了flashback table的功能。从SQL PLAN中也可以看到,flashback至少需要在表上作一次full table scan,如果表很大而且修改频繁,其IO成本可想而知。还可以研究一下SYS_FBT_INSDEL function and FBTSCAN hint
如果已经提交,就要用闪回,flashback到一个时间点