We are trying to expand column in one of our tables from Numeric-4 to Integer.

Here is statement: ALTER TABLE "DBA"."ps_detail" ALTER "brand" INTEGER;

We are getting an error:

Run time SQL Error -- *** ERROR *** Assertion Failed: 109900
(12.0.1.4142) [Backup]
Error decoding ROWID
SQLCODE=-300, ODBC 3 State-"HY000"

Can any one give me a sense of what this error means and how I can avoid it?

Thanks, Brian

asked 05 Feb '15, 08:58

Brian's gravatar image

Brian
76557
accept rate: 0%

edited 05 Feb '15, 14:47

Breck%20Carter's gravatar image

Breck Carter
27.4k424585836

[Backup] Error ...

Does it mean there is a backup running while you are running the ALTER TABLE statement? - IMHO, that would come as a surprise as ALTER TABLE will do an initial checkpoint and will delay other checkpoints, and that should prevent a BACKUP statement, too, as that does its own checkpoint, cf. the docs:

A checkpoint is carried out at the beginning of the ALTER TABLE operation, and further checkpoints are suspended until the ALTER operation completes.

(Yep, that's another way of saying "I don't have a clue, sorry!")

(05 Feb '15, 10:19) Volker Barth

This non-fatal assertion is one I don't think I have ever seen.

It is reporting a problem related to a row lookup in database "Backup" trying to determine the Id of a row. It does not seem to be related to data in any row ... its more about existence of and valid IDs for specific rows.

The biggest concern is that this was occurring during an Alter Table operation. I hope you did a backup just prior to doing this.

I wonder if this was a 1-time/transient problem or if it is reproducible. If reproducible then I would want to check the validity of this table and start looking into aspects of the column "brand" has any interesting characteristics associated with it (indexed, pkey column, fkey column, check constraints, defaults ...) it may be worth discussing this with support.

So I would recommend at least validate the "DBA"."ps_detail" table, both with a copy from the backup (from before the alter) and against the after copy. You will also want to verify what the resulting type is for the column "brand" is valid. If still numeric(4) I would also attempt the conversion again.

HTH

(05 Feb '15, 13:19) Nick Elson S...

I dropped all indexes for this table, ran ALTER statement, then re-added indexes. Everything worked fine using this process. In my prior tests, I had not dropped any indexes. The brand column in question is part of the primary key and several of the indexes.

(05 Feb '15, 16:59) Brian

Jason's answer would be a correct analysis. My main concern is there could be other issues inside that database. Rebuilding the database may reveal, correct and/or prevent other issues that may be present. At a minimum, you will want to do backups and validate what you have. {index or any such corruption should not be a normal result of using the software}

(06 Feb '15, 11:03) Nick Elson S...

It sounds like there may have been some index corruption and that the corruption was preventing the server from deciphering a valid ROWID, the location of the row, from the index. Dropping the primary key likely resolved this.

permanent link

answered 06 Feb '15, 09:34

Jason%20Noack's gravatar image

Jason Noack
85611116
accept rate: 75%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×31
×11

question asked: 05 Feb '15, 08:58

question was seen: 552 times

last updated: 06 Feb '15, 11:03