Drop table without its dependencies and solve block corruption

oracleoracle-10gtable

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?

————————Edit

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!

Best Answer

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
dba_objects
--  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:

RMAN> BACKUP VALIDATE CHECK LOGICAL DATAFILE 333;

And drop filler table:

DROP TABLE filler PURGE;

Easy. :)

Related Question