My Oracle Support Banner

Steps for Handling (Salvage) Block Corruption in Large Partitioned tables using EXCHANGE PARTITION method when no Backups available (Doc ID 1324772.1)

Last updated on FEBRUARY 14, 2022

Applies to:

Oracle Database - Enterprise Edition - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
There are corrupted blocks (e.g. ORA-1578) in partitioned table and need to get rid of these corrupted blocks instead of using DBMS_REPAIR method (in case it can be used) as it does not remove the corrupted block.



When a corruption is identified in a huge table partition block (e.g. ORA-01578), and we don't have backups (e.g. RMAN, OS level, Export, or any external resource) to recover the corruption, we can still try to salvage the remaining data in the table using 10231 event (with some possible data loss and inconsistency by skipping corrupted data blocks).

One method to achieve this is:

a) create a salvage table (using 10231 event) and insert data from corrupted partition (using CTAS or INSERT INTO SELECT...) :

b) truncate the partition (using alter table <original_table> truncate partition <partition_name>) or DELETE the rows in partition

c) Insert the data from salvage table to truncated corrupted partition (using INSERT INTO SELECT...)

In above method, step c) will take significant amount of time depending on the number of rows in the partition i.e. if the table partition contains large number of rows.

We can significantly reduce the time taken in step c) above by using EXCHANGE PARTITION method which can be used with partitioned tables.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.