After back and forth email conversation with max and I, we were able to re-initialize the blocks, resolving the corruption issue. Here is a small writeup about this issue and scripts to resolve it, hopefully, this blog entry will be useful for someone in the future: http://orainternals.wordpress.com/2014/07/11/how-to-reformat-corrupt-blocks-which-are-not-part-of-any-segment/ Cheers Riyaj Shamsudeen Principal DBA, Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS Blog: http://orainternals.wordpress.com/ Oracle ACE Director and OakTable member <http://www.oaktable.com/> Co-author of the books: Expert Oracle Practices <http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL, <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices <http://tinyurl.com/book-expert-plsql-practices> <http://tinyurl.com/book-expert-plsql-practices> On Fri, Jul 11, 2014 at 10:49 AM, Riyaj Shamsudeen < riyaj.shamsudeen@xxxxxxxxx> wrote: > Database has many data files for system tablespace. I need to adjust the > script a bit. > > Let's try the following script: > > drop table test1; > create table test1 (n number, v varchar2(2048)) tablespace system; > > select * from dba_free_space where tablespace_name='SYSTEM' order by bytes; > > set serveroutput on size 100000 > declare > begin > for c1 in ( > select bytes from dba_free_space > where tablespace='SYSTEM' > order by bytes desc ) > loop > dbms_output.put_line ('alter table test1 allocate extent ( size '|| > c1.bytes ||')' ); > execute immediate 'alter table test1 allocate extent ( size '|| > c1.bytes ||')'; > end loop; > end; > / > > select distinct f.file_id, f.block_id, f.bytes, f.blocks > from dba_free_space f join v$database_block_corruption c > on (c.block# between f.block_id and f.block_id + f.blocks -1 > and f.file_id =c.file#) > where f.file_id=1 > / > > select * from dba_free_space where tablespace_name='SYSTEM' order by bytes > / > > Cheers > > Riyaj Shamsudeen > Principal DBA, > Ora!nternals - http://www.orainternals.com - Specialists in Performance, > RAC and EBS > Blog: http://orainternals.wordpress.com/ > Oracle ACE Director and OakTable member <http://www.oaktable.com/> > > Co-author of the books: Expert Oracle Practices > <http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL, > <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert RAC > Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices > <http://tinyurl.com/book-expert-plsql-practices> > > <http://tinyurl.com/book-expert-plsql-practices> > > >