Example 2-7 Finding LOB Corruption in Large Tables

Use this script to verify LOB corruption, and to find and store the corrupted LOB IDs in a temporary table so that you can then exclude them from the table you want to export.

1. Create a new temporary table for storing all rowids called corrupt_lobs

   SQL> create table corrupt_lobs (corrupt_rowid rowid, err_num number);
       

2. Make a desc on the large table <TABLE_NAME> containing the LOB column:

   DESC <TABLE_NAME>
   
   Name         Null?     Type 
   ----------   --------- ------------
   <COL1>       NOT NULL  NUMBER 
   <LOB_COLUMN>           BLOB 

   Run the following PL/SQL block:

   declare 
     error_1578 exception; 
     error_1555 exception; 
     error_22922 exception; 
     pragma exception_init(error_1578,-1578); 
     pragma exception_init(error_1555,-1555); 
     pragma exception_init(error_22922,-22922); 
     n number; 
   begin 
     for cursor_lob in (select rowid r, <LOB_COLUMN> from <TABLE_NAME>) loop 
     begin 
       n:=dbms_lob.instr(cursor_lob.<LOB_COLUMN>,hextoraw('889911')); 
     exception 
       when error_1578 then 
         insert into corrupt_lobs values (cursor_lob.r, 1578);
         commit; 
       when error_1555 then 
         insert into corrupt_lobs values (cursor_lob.r, 1555);
         commit; 
       when error_22922 then 
         insert into corrupt_lobs values (cursor_lob.r, 22922);
         commit; 
       end; 
     end loop; 
   end; 
   / 

   The result of this PL/SQL script is that all rowids of the corrupted LOBs will be inserted into the newly created corrupt_lobs table.

3. Resolve the issue with the corrupted LOB rowids either by emptying the corrupted LOB rows, or by exporting the table without the corrupted LOB rows.

   • Empty the corrupted LOB rows

     With this option, you run a SQL statement to empty the rows. In this example, the rows that you select are BLOB or BFILE columns, so we use EMPTY_BLOB. For CLOB and NCLOB columns, or use EMPTY_CLOB:

     SQL> update <TABLE_NAME> set <LOB_COLUMN> = empty_blob() 
          where rowid in (select corrupt_rowid from corrupt_lobs);

   • Export the table without the corrupted LOB rows

     Use this script with values for your environment:

     $ expdp system/<PASSWORD> DIRECTORY=my_dir DUMPFILE=<dump_name>.dmp LOGFILE=<logfile_name>.log TABLES=<SCHEMA_NAME>.<TABLE_NAME> QUERY=\"WHERE rowid NOT IN \(\'<corrupt_rowid>\'\)\"

For more information about identifying and resolving ORA-1555 errors, and distinguishing them from LOB segment issues due to LOB PCTVERSION or RETENTION being low, see the My Oracle Support document "Export Receives The Errors ORA-1555 ORA-22924 ORA-1578 ORA-22922 (Doc ID 787004.1"