Number with a length of -1?

From: Dba DBA <>
Date: Fri, 4 May 2012 14:35:50 -0400
Message-ID: <>

DB Version:
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_at_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. *


When I query in sqlplus I get back .0000 When I query from sql developer I get 6.5.4345

Two different values.

Received on Fri May 04 2012 - 13:35:50 CDT

Original text of this message