block corruption in data dictionary tables

[D Maverick]

Guys, how can i resolve block corruption in data dictionary tables? if its from a user table i can use the Analyze Table command but i cant use the command for Views.

i got this error:

SQL> SELECT * FROM DBA_SOURCE;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 1, block # 4478)
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\EIC\SYSTEM01.DBF'

thank you very much for your help.

[sanjaygk]

Hi,

Data dictionary tables will be residing in system tablespace.
if any datafile is corrupted then copy the datafile to different location, rename the file then recover the database.

[jmodic]

First try to determine whether the specified block belongs to a table or to an index. You can use DBA_EXTENTS to find out that:

select owner, segment_name, segment_type from dba_extents
where file_id = 1
and 4478 between block_id and block_id + blocks;

If the offended block belongs to the index segment you can simply recreate it. If it belongs to a table then I belive you'll have to recreate the database.

[D Maverick]

Originally posted by jmodic
...I belive you'll have to recreate the database.

I think so =) thanks for the help.
BTW, how can i prevent it from happening again? any suggestions on how i can detect block corruptions other than checking the alert log and running ANALYZE TABLE / INDEX command?

Thanks again.

[aarroyob]

Hi,

For detect the corruption you have the dbv utility.

Then if you have the name of the corrupted file, you can know wich segment is corrupted:

1. select file_name, file_id
from dba_data_files
where file_name like '%your_file%';

2. select owner,segment_name,segment_type
from dba_extents
where file_id= file_id_of_the_first_select
and number_of_block_obytained_with_dbv between block_id and block_id+blocks-1;

This will show you the all as you need.

Other thing you must know is that all database blocks are formatted before Oracle use, then if the block is not assigned, no problem it will be corrected, and if it is assigned, you can drop the segment and recreate (not in your case because the corruption is in the system tablespace).

Your solution is recreate the database.

Good Luck

Regards

Angel