I have found alert about corrupted block in alert log. Corrupted block belonged to the table named DOCS. Because of I don't have any backup of this database, decided to recreate DOCS table to solve the corruption. And here is a problem.

Generated all scripts:

1. DDL for table.
2. DDL for constraints,triggers,indexes on the table.
3. DDL for the constraints that references this table.
4. Saved grants on this table.

Now I want to drop table , but left its dependencies , and then recreate table with its constraints,triggers,indexes..

How can I do that?


It seems to be very simple.

  1. Save data of this table

    create table new_table as select * from my_table;

  2. Save DDL for table and its comments
  3. DDL for indexes on this table
  4. DDL for constraints on this table
  5. DDL for Foreign key-s that references on this table
  6. Save grants on this table
  7. Drop table purge
  8. Recreate table, comments, constraints, indexes, foreign keys that references to this table
  9. Give grants
  10. insert data

    insert into my_table as select * from new_table

This solution works for re-creation the table(it is a correct way to recreate a table). But the corruption did not solved, during the insertion into the table in Alert.log raised the same error about block corruption.

In comments @Mindaugas Riauba wrote a solution and it worked. I just want to know why my solution about re-creation table did not work and @Mindaugas Riauba's solution work?

Thank you @Mindaugas Riauba again!

  • I've tested now and if you drop all constraints, then drop table .. child records remain itself :)
    – kupa
    Dec 20, 2013 at 16:33
  • Why not just truncate the table? Data blocks will be released to free space. BTW you can pull almost all the data from the table using SELECT ... INSERT. And after DROP or TRUNCATE corrupted block may still be corrupted and mess with backup. To clean that you will need to fill datafile with corrupted block. Dec 20, 2013 at 19:03
  • @MindaugasRiauba you are right, after dropping a table corrupted blocks are still corrupted. I've read somewhere on the internet that after dropping a segment containing the corrupted block solves the corruption but it is not in 10g Oracle as I see. If I don't have backup how can I solve this problem? Can I move segments from this datafile to another one and drop this datafile after that?
    – kupa
    Dec 21, 2013 at 14:19

1 Answer 1


Apparently you dropped the object already. Now to calm you down - corrupted blocks now most probably are in free space and do no harm. They just annoy you during backups. To check that:

SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
  , greatest(f.block_id, c.block#) corr_start_block#
  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
  - greatest(f.block_id, c.block#) + 1 blocks_corrupted
  , 'Free Block' description
from dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file# AND f.block_id <= c.block# + c.blocks - 1
  and f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;

If it really shows here you have two options. You can simply ignore it. Once the block will be assigned to some object it will be reformated and corruption will go away. If you want to fix corrupted blocks then you will have to create the object which would occupy your corrupted blocks.

Let's say your corrupted block exists in tablespace USERS and datafile /ora/users01.dbf. First you have to make datafiles of tablespace not autoextendable. That is not to inflate datafile size. And then you'll create filler table.

create table filler (
  padding varchar2(1000)
) tablespace users nologging pctfree 90 pctused 10;

Then you have to extend table's segment so that it would incorporate corrupt blocks:

alter table filler allocate extent
  (datafile '/ora/users01.dbf' size 100m);

In such way you can add several extents adjusting their sizes. After you'll check that corrupt blocks now longer belong to free space you can fill the table:

insert /*+append */ into filler  select rpad('x',1000) from
--  filler
where rownum <= 50000;

First insert/select goes from dba_objects then it is faster to insert/select from filler table itself.

Now you just have to check datafile using RMAN:


And drop filler table:


Easy. :)

  • you are great! but I will check your solution now... but a little thing I did not understand. While I dropped the table containing corrupted block, and then recreated it with the same parameters the error still raised during the creation, according your reply during the re-creation the table the corrupted block should have been re-initialized and corrected, why not happened so?.(I did it before I read you post, will try now and write what happens)
    – kupa
    Dec 27, 2013 at 8:24
  • Please update your post with the copy/paste of new error. And where did you see that error? Alert log? Somewhere else? Dec 27, 2013 at 9:25
  • You are a rock! :) but don't understand my table re-creation did not work :(
    – kupa
    Dec 27, 2013 at 10:20
  • Your solution worked. But my solution that, I think is the same as your did not work, why? /*Please, see my updated post*/
    – kupa
    Dec 27, 2013 at 11:21
  • I think that after you reinserted your records into the table that data went into other (not corrupted) data blocks. You could have used SELECT I provided to check that. After you dropped the table extents used by the table were released into free space. Dec 27, 2013 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.