Re: Extracting Data from a Corrupt Table using ROWID scans

From: coeval <hvfrench_at_gmail.com>
Date: 24 Jun 2005 06:23:32 -0700
Message-ID: <1119619412.699356.14330@g44g2000cwa.googlegroups.com>

Hi ,

I found the following code on the Web but I don't understand how to use

the second query with Oracle 9i table full of VARRAY, TYPE, NESTED TABLE.

---

REM Created: 02/20/01 - Trifon Anguelov

REM Find out what object is corrupted

SELECT segment_type,

       owner,
       segment_name

  FROM dba_extents
WHERE file_id = (file_num_from_err_mesg) AND

      (block_num_from_err_mesg) BETWEEN block_id AND (block_id + blocks-1);

REM Shows the rows in the corrupted database blocks

SELECT DISTINCT (key)
  FROM (corrupted_table)
WHERE key > (minimum_key_value) AND

      SUBSTR(rowid, 1, 8) = (corrupted_block_id) ORDER BY 1; REM Create table with the rest of the good data in the corrupted object

CREATE TABLE (new_table) AS
  SELECT * FROM (corrupted_table)
    WHERE key > (minimum_key_value) AND

          key NOT IN ( SELECT DISTINCT (key)
                         FROM (corrupted_table)
                       WHERE key > (minimum_key_value) AND
                             SUBSTR(rowid, 1, 8) =

(corrupted_block_id)); Received on Fri Jun 24 2005 - 08:23:32 CDT