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

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Fri, 11 Jul 2014 10:24:01 -0700
Message-ID: <CAA2Dszxmsm7chq+cV+Lien7LmNXas_V1LQucRQObyViz8hr3Ug_at_mail.gmail.com>



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>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jul 11 2014 - 19:24:01 CEST

Original text of this message