oracle 数据恢复
时间:2015-02-11 19:32 来源:linux.it.net.cn 作者:IT
1、delete后恢复
最好是立即进行恢复
insert into mytest
( select *
from mytest as of timestamp to_timestamp('2013-07-11 15:18:04','yyyy-mm-dd hh24:mi:ss'))
2 drop table 恢复
a 先确保 show parameter recyclebin 状态是 on
b select * from recyclebin / show recyclebin
select * from recyclebin where original_name='MYTEST';
c、如果有对应的表，则进行恢复
flashback table mytest to before drop
ok，搞定
其他sql语句总结
清空回收站
purge recyclebin;
清空所有用户的回收站
purge dba_recyclebin;
批量生成数据
create table mytest as
select rownum as id,
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as inc_time,
trunc(dbms_random.value(0,1000)) as random_id,
dbms_random.string('x',20) as random_string
from dual
connect by level<=1000;
查询可用空间
SELECT text,view_name FROM dba_views WHERE view_name='DBA_FREE_SPACE';
select text,view_name from dba_views
查询当前的scn
select dbms_flashback.get_system_change_number from dual;
-- 548966
查询符合条件的scn，并且保持到临时表
create table scn_temp(count int,scn int);
declare
i int :=548000;
begin
for i in 548000 .. 548966 loop
insert into scn_temp(scn)values(i);
update scn_temp set count=(
select count(*) from ccc as of scn i) where scn=i;
end loop;
end;
//推荐使用这种方式
select * from ccc as of timestamp to_timestamp('2013-07-12 00:09:22','yyyy-mm-dd hh24:mi:ss');
删除索引
drop index "index_bbb";//不能少了引号
(责任编辑：IT)
| 1、delete后恢复 最好是立即进行恢复 insert into mytest ( select * from mytest as of timestamp to_timestamp('2013-07-11 15:18:04','yyyy-mm-dd hh24:mi:ss')) 2 drop table 恢复 a 先确保 show parameter recyclebin 状态是 on b select * from recyclebin / show recyclebin select * from recyclebin where original_name='MYTEST'; c、如果有对应的表，则进行恢复 flashback table mytest to before drop ok，搞定 其他sql语句总结 清空回收站 purge recyclebin; 清空所有用户的回收站 purge dba_recyclebin; 批量生成数据 create table mytest as select rownum as id, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as inc_time, trunc(dbms_random.value(0,1000)) as random_id, dbms_random.string('x',20) as random_string from dual connect by level<=1000; 查询可用空间 SELECT text,view_name FROM dba_views WHERE view_name='DBA_FREE_SPACE'; select text,view_name from dba_views 查询当前的scn select dbms_flashback.get_system_change_number from dual; -- 548966 查询符合条件的scn，并且保持到临时表 create table scn_temp(count int,scn int); declare i int :=548000; begin for i in 548000 .. 548966 loop insert into scn_temp(scn)values(i); update scn_temp set count=( select count(*) from ccc as of scn i) where scn=i; end loop; end; //推荐使用这种方式 select * from ccc as of timestamp to_timestamp('2013-07-12 00:09:22','yyyy-mm-dd hh24:mi:ss'); 删除索引 drop index "index_bbb";//不能少了引号 (责任编辑：IT) |