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

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Fri, 11 Jul 2014 10:49:24 -0700
Message-ID: <CAA2DszygP8oO45D1VmqYxi65b2HuWCDfZgxGm20Pd7AfXwY20Q_at_mail.gmail.com>

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>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jul 11 2014 - 19:49:24 CEST