RE: Number with a length of -1?

From: Jeffrey Beckstrom <>
Date: Fri, 04 May 2012 15:15:28 -0400
Message-Id: <>

I vaguely recall that a very long time ago there was an issue where some PRO*x programs could insert bad data into the database without being caught. That's all I recall about it at the moment.  

Jeffrey Beckstrom
Database Administrator
Greater Cleveland Regional Transit Authority Information Systems
1240 W. 6th Street
Cleveland, Ohio 44113
>>> "Taylor, Chris David" <> 5/4/12 2:50 PM >>>
If you use rman you can try "RMAN> backup validate check logical database"

That will check for logical corruption but still may not find your particular issue.

Chris Taylor

"Quality is never an accident; it is always the result of intelligent effort." -- John Ruskin (English Writer 1819-1900)

Any views and/or opinions expressed herein are my own and do not necessarily reflect the views of Ingram Industries, its affiliates, its subsidiaries or its employees.

-----Original Message-----
From: [] On Behalf Of Dba DBA Sent: Friday, May 04, 2012 1:36 PM
Subject: Number with a length of -1?

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 - 14:15:28 CDT

Original text of this message