Re: QUESTION: Corrupt data block work around

In article <1993May13.144359.12691_at_kynug.org> Neil Greene <neil_at_kynug.org> writes:
> I have managed to find a corrupt data block in my Oracle database. The
> database runs fine, except when attempting to access data within this
> corrupt data block. Luckily enough, all updates, changes and inserts
 are
> using indexes to get around this current problem, while any attempts to
> export this table file because of the bad block.
>
> I am now attempting to make use of ROWID to get around looking at data
 in
> the corrupted block; but, this has been done with no success.
>
> Is the rowid field indexed?? Is there a select statement I can build
 that
> will simply fetch all rows not found in the corrupt datablock. If so, I
> could dump them to a file, take the database down, make a full backup
 and
> load the data back into a newly created table using SQL*Loader.
>
> The corrupt data is in block #4002 or file #8. File #8 contains our
> ruling information. Here is a layout of the ruling table, fields marked
> with an asterisks are indexed:
>
> > SQL> describe rulings;
> > Name Null? Type
> > ------------------------------- -------- ----
> > RULINGID NOT NULL NUMBER(7) *
> > NASRISID NOT NULL NUMBER(7) *
> > RULINGDATE DATE *
> > RULINGNO CHAR(1)
> > TRACK CHAR(3) *
> > STATE CHAR(3) *
> > LICENSETYPE CHAR(3)
> > DIVISION CHAR(1)
> > COMMISSION CHAR(3) *
> > COMMISSIONREF CHAR(13)
> > RULINGTYPE CHAR(2)
> > BATCHVOLUME CHAR(3) *
> > BATCHNUMBER CHAR(4) *
> > RULINGTEXT LONG
> > DRUGNAME CHAR(40)
> > ENTRYDATE DATE
For those of you following along in this little game. I have verified that accessing all of the records in the rulings table through the nasrisid index sucessfully looks at all of the records in this table. Now, the problem is to sucessfully copy the data from this table using this index to a newly created table. The only problem is with the "rulingtext" field which is of type long. It is my understanding that creating a new table with this field will not be successful and that I will either have to dump the data to disk, or use pro*c to access this field. If I am not mistaken, sql*plus is going to limit the line length to 500 characters, which may fall far below my 2000 character limit of a long datatype. Is anyone still listening?? :-)

Any takers for a solution?

-- 
Neil Greene
President, Kentucky NeXT User Group, Inc.
Email: neil_at_kynug.org [NeXTMail]