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.
- Save data of this table
create table new_table as select * from my_table;
- Save DDL for table and its comments
- DDL for indexes on this table
- DDL for constraints on this table
- DDL for Foreign key-s that references on this table
- Save grants on this table
- Drop table purge
- Recreate table, comments, constraints, indexes, foreign keys that references to this table
- Give grants
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!