一种Oracle的批量清理空free块坏的方法

VALIDATE database;

查询坏块列表

select * from V$DATABASE_BLOCK_CORRUPTION;    
select file# from V$DATABASE_BLOCK_CORRUPTION group by file# order by file#;--一共涉及到多个文件，并排序。

 
删除上次检查的临时坏块列表

drop table cb7; 
create table cb7 as select * from V$DATABASE_BLOCK_CORRUPTION;    
select * from cb7;

查询free-block-获取exteand

drop table fblock;
create table fblock as Select d.name,f.BYTES bytes,c.block# block#,c.file# file# from dba_free_space f,cb7 c,v$datafile d  where d.file#=c.file# and file_id=c.file# and c.block# between f.block_id and f.block_id + f.blocks -1;
select * from fblock;

批量创建表

select 'create  table  test.s'||block#||'  (n  number,c varchar2(4000) )nologging  tablespace  test pctfree 99;'  from    fblock;

快速填充free-block

select '
BEGIN
for i in 1..1000000 loop
EXECUTE IMMED