Block corruption in oracle table

1) Backup your database right now! Before we start doing any sort of repairs. Do you know how to do this? I advise using RMAN.
2) Are you running in archivelog mode?

Below is how to run RMAN from the Oracle user, do a backup, then also validate the database. RMAN will then mark blocks that are corrupt into a corruption list that you can work with. You may have a tiny problem or a very large problem.

-- First do the backup. It MAY fail if you have media problems, we will see.
rman target /
RMAN> configure controlfile autobackup on;
RMAN> backup database;

-- If it fails

-- And then this step doesn't really create a backup, but validates blocks and populates the V$DATABASE_BLOCK_CORRUPTION view
RMAN> backup validate database;

-- Then login via sqlplus and check how many blocks are bad

sqlplus / as sysdba
select * from V$DATABASE_BLOCK_CORRUPTION;


3) You have no options to _recover_ blocks, since you have no backups. The only option is to mark blocks or rebuild the table while skipping them. You can play around with some SQL and try to select subsets of data from the table to see if certain ranges succeed. If, for example, the block corruption is in a given primary key range, if you query using a divide and conquer approach, you can narrow down where the problem is.

select * from table where id < 1000;
-- block error
select * from table where id < 500;
-- succeeds
select * from table where id >= 500 and id < 750;
-- etc. until you narrow down


You can also use DBMS_REPAIR to do all of the analysis, and mark the bad blocks.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#i1006401

First, see how bad the problem is, then let me know.

If you have archive logs, you can use Oracle LogMiner to recover all of the DML statements for all of the log history you have. So it depends on how far back you can / need to go.

If you had a successful RMAN backup, you could follow the steps above I listed

1) If you know the block # that is corrupt (assuming it is a single block)
RMAN> recover datafile 7 block 123444;

2) If there are many blocks, use:
RMAN> backup validate database;
RMAN> recover corruption list;

In some cases you might have to specify an until clause because even the most recent backup may still have block errors. This is why a good retention policy is important. It may take days, weeks or months to notice a block error. However, if using RMAN in the first place, you'll know about it fast. This is one reason why RMAN is preferred over user-managed backups.