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

  • From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
  • To: max scalf <oracle.blog3@xxxxxxxxx>
  • Date: Fri, 11 Jul 2014 10:24:01 -0700

Hi
  I can't guarantee that this will work in one try, however, we will have
to improvise. Let's execute the following query and check the output. If
you want to see just the output, before executing PL/SQL block, comment out
execute immediate line.

  (I am having difficulties in testing, as I have no dictionary managed
tablespaces and can't create one either).

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
 l_file_id number := 1; -- for the system tablespace data file 1
begin
   for c1 in (
     select  bytes from dba_free_space
     where file_id =l_file_id
     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: