Number with a length of -1?

DB Version: 10.2.0.5.6
This is a very old database that may date back to 6.x.
We have a very old job that does replace(to_char(field)) and blows up on
some data. When I look at it I notice that it has a length of -1. Any idea
how that can get in the DB? When I look for 'invalid length' errors I see
issues with variables not with the data actually making it into the DB. I
have a ticket open with Oracle on the ORA-600 and ORA-7445 error (I looked
them up. None of the published issues a directly related to this). I need
support for ORA-600 stuff

To be clear. I can query the data. I just can't use certain functions on
it. This is very old code and I can't change the code. This has happened
with more than 1 table and I have to spend alot of time narrowing it down
to the correct record and the correct field (on very large tables).

I came here for a couple of things. If anyone has any idea how this could
happen?
Anyone have any suggestions for how to find corrupt data in the DB?
I do not have block corruption. I already tried analyze table.. validate
structure and it worked. I tried copying the data to a new table. The data
is still bad. (did create table as)

Is this something I can us the character set scanner to identify? How do I
find corrupt data? When I google it, I keep finding ways to find corrupt
blocks. I just need to find the bad records and delete them. Querying every
possible table and every possible field looking for stuff that will blow
up, isn't really practical. There is alot of data, tables, and fields.

DB Size: 10 TB+ So I can't export it or use data pump. its just too big. I
saw that method on Support.
Our code can do a replace(to_char(field) on many tables and many fields. We
just failed on a different table that is 40 gbs.
Anyone know of a tool that can scan the whole database?

When I looked in the first table that failed I found some odd things.

This is the output for one of the bad values. I was able to narrow it down
to 1 field and 1 value.
table names and field names have been changed.

*
*  1      select rawtohex(myvar) myrawtohex ,dump(myvar)
mydump,length(myvar) mylength
  2       FROM myvar
  3       where
  4*       rowid = 'badrowid
A325KJ@orpt3> /

MYRAWTOHEX           MYDUMP                                     MYLENGTH
-------------------- ---------------------------------------- ----------
C00000               Typ=2 Len=3: 192,0,0                             -1

No idea what a HEX of C0000 when I look that I up I keep getting windows
blue screen of death errors and nothing about data encoding. *

*FIELD : NUMBER(15,3)
When I query in sqlplus I get back .0000
When I query from sql developer I get 6.5.4345

Two different values.
*
*


--
http://www.freelists.org/webpage/oracle-l

Other related posts: