Re: ERROR: SAVE Undo Block Corrupted. Error Code = 50

  • From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
  • To: max scalf <oracle.blog3@xxxxxxxxx>
  • Date: Sat, 12 Jul 2014 09:02:18 -0700

Yes.  Originally I thought your system table space had just one datafile.
So I didn't bother.

Now thinking further I should update the script to allocate to a data file
- it might be handy if there are numerous data files in that Tablespace.
But that would also require to alter the table such that it wouldn't
allocate more space in other datafile during the insert code.

Thanks for noticing it.
On Jul 12, 2014 8:07 AM, "max scalf" <oracle.blog3@xxxxxxxxx> wrote:

> Riyaj,
>
> Thank you for your help again.  I had a question with regards to the
> procedure we used to re-initialize those empty blocks.  What we did was
> filled up the entire tablespace(SYSTEM in this case), but i was wondering
> if we could have gotten away with just filling up the affect datafile by
> allocating extents out of that file only by using alter table test1
> allocate extent (datafile <datafile_name> size XX)?
>
>
>
> On Fri, Jul 11, 2014 at 3:23 PM, Riyaj Shamsudeen <
> riyaj.shamsudeen@xxxxxxxxx> wrote:
>
>> 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>
>>>
>>>
>>>
>>
>

Other related posts: