oracle数据恢复


oracle数据恢复
 
oracle 数据恢复 drop del
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";//不能少了引号