No question at this time
DBA Top 10
1 M. Cadot 15900
2 B. Vroman 15500
3 A. Kavsek 10000
4 T. Boles 6800
5 J. Schnackenberg 5800
6 M. Hidayathullah ... 3900
7 P. Wisse 1800
8 T. P 700
9 B. Derous 500
9 R. Wauben 500
9 H. Steijntjes 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48208
Total active users1645
Act. users last 24h7
Act. users last hour0
Registered user hits last week163
Registered user hits last month788
Go up

ORA-01499: table/index cross reference failure - see trace file
Next thread: Impact of parallelism on session_per_user limit
Prev thread: multiple port on single IP in listener

Message Score Author Date
Hi Guys I have table that keeps on having table...... Tso P Feb 20, 2018, 11:00
Dear, please check following mos note troublesh...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Mirza Hidayathullah Baig Feb 20, 2018, 11:40
Thanks Mirza These are very useful docs. Muc...... Tso P Feb 20, 2018, 18:37
What does the below mean? The affected table ha...... Tso P Feb 20, 2018, 19:51
Hi, please check this link, you may get some in...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Mirza Hidayathullah Baig Feb 20, 2018, 20:19
(reply to original question) Hello Tso,   <<<I...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Bruno Vroman Feb 21, 2018, 12:33
(reply to "COLUMN_NAME" VARCHAR2(1 CHAR) DEFAULT N...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Bruno Vroman Feb 21, 2018, 12:44
Thanks Bruno Maybe what you should do is mo...... Tso P Feb 21, 2018, 19:23
Hello Tso,   <<<I>Still I could not <b>analyse<...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Bruno Vroman Feb 22, 2018, 14:03
Thanks Bruno I really appreciate!!! I have a...... Tso P Feb 23, 2018, 09:19
Thanks everyone for the replies... Oracle sugge...... Tso P Feb 27, 2018, 07:47
Thanks Bruno I have being reading Tom Kyte book...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Tso P Feb 27, 2018, 22:17

Follow up by mail Click here


Subject: ORA-01499: table/index cross reference failure - see trace file
Author: Tso P, South Africa
Date: Feb 20, 2018, 11:00, 120 days ago
Os info: solaris 10
Oracle info: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Message: Hi Guys

I have table that keeps on having table row key mismatch with three indexes and that creates a slow responds on the application.

It happened twice and I found out that I would have a row with funny characters.

I would have to drop these three indexes then update the row affected. Then backup the table with CTAS, truncate the table then insert data from the backup table.

I have read articles from the net but I have not yet found an article that points what is the cause...

What might be the cause of this issue...

Thanks in advance...

Please help!!!
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01499: table/index cross reference failure - see trace file
Author: Mirza Hidayathullah Baig, United Kingdom
Date: Feb 20, 2018, 11:40, 120 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Dear,

please check following mos note troubleshooting guide, it will take to relevant notes and docs.

Troubleshooting Assistant: Get Assistance to understand and solve Oracle Database Server Corruptions (Doc ID 1543698.2)

Quick method to identify table/index mismatch when analyze validate structure cascade takes significant time (Doc ID 1554054.1)

ORA-1499. Table/Index row count mismatch (Doc ID 563070.1)

Regards
Mirza
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01499: table/index cross reference failure - see trace file
Author: Tso P, South Africa
Date: Feb 20, 2018, 18:37, 120 days ago
Message: Thanks Mirza

These are very useful docs.

Much appreciated!!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01499: table/index cross reference failure - see trace file
Author: Tso P, South Africa
Date: Feb 20, 2018, 19:51, 120 days ago
Message: What does the below mean?

The affected table have a column defined in this way...

"COLUMN_NAME" VARCHAR2(1 CHAR) DEFAULT NULL NOT NULL ENABLE

Please Help!!!

Thanks in advance...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01499: table/index cross reference failure - see trace file
Author: Mirza Hidayathullah Baig, United Kingdom
Date: Feb 20, 2018, 20:19, 120 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi,

please check this link, you may get some insight,still not sure

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2575782100346318728

Regards
Mirza
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01499: table/index cross reference failure - see trace file
Author: Bruno Vroman, Belgium
Date: Feb 21, 2018, 12:33, 119 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message: (reply to original question)
Hello Tso,

  <<I would have to drop these three indexes then update the row affected. Then backup the table with CTAS, truncate the table then insert data from the backup table.>>

Isn't this "too much - and not enough"?
If you have to drop the indexes to be able to update the affected row, why do you need to move all the data to a backup table and then move it back to original table? (this is what I call "too much")
And anyway: at the end the three indexes are still "gone" (this is what I call "not enough"): they have to be rebuilt.

Maybe what you should do is more simply;
a) drop the indexes
b) update the data to fix the row
c) create indexes


Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01499: table/index cross reference failure - see trace file
Author: Bruno Vroman, Belgium
Date: Feb 21, 2018, 12:44, 119 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: (reply to "COLUMN_NAME" VARCHAR2(1 CHAR) DEFAULT NULL NOT NULL ENABLE ...)

Hello,

this means that the column cannot be NULL in the database (the NOT NULL constraint is enabled) but that there is no default value provided.

If you define for example a table with a column COLUMN_NAME having a default value of 'x', you can do things like
INSERT INTO mytable( mycol ) VALUES ( 123 );
and have:
SELECT * FROM mytable WHERE mycol = 123;

MYCOL COL2 COLUMN_NAME COLN
----- ---- ----------- ----
123 x
and this can be convenient if the column COLUMN_NAME is defined as "NOT NULL" in the database. But here you have no default value proposed, so the same "insert" would fail if the column is defined as NOT NULL and we are forced to do things like
INSERT INTO mytable( mycol, column_name ) VALUES ( 445, 'a' );

Remark: I don't see the benefit of declaring "DEFAULT NULL" compared to not declaring default.

Best regards,

Bruno.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01499: table/index cross reference failure - see trace file
Author: Tso P, South Africa
Date: Feb 21, 2018, 19:23, 119 days ago
Message: Thanks Bruno



Maybe what you should do is more simply:
a) drop the indexes
--I dropped the indexes
b) update the data to fix the row
--I have updated the row
c) create indexes
--recreated the indexes

Still I could not analyze the table after the steps above...I had to truncate as I mentioned but that is too much as you said.

What might be the cause of this issue as it happens for the second time?

Much appreciated!!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01499: table/index cross reference failure - see trace file
Author: Bruno Vroman, Belgium
Date: Feb 22, 2018, 14:03, 118 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hello Tso,

  <<Still I could not analyse the table after the steps above>>

you are scaring me... I hope that you don't use the "ANALYZE TABLE" command but the DBMS_STATS package (that is the recommended way for several releases now)

About the cause of the corruption, I have no clue. For your info I had a few times "index corruptions" many years ago because some sessions were inserting new data 'too quickly' (and the "fix" was to drop and recreate the index, as "rebuild" was not OK) but my table data was OK.

If you have to truncate the table, then at least rebuild the indexes only after having moved the data back:

drop all the indexes on the table (make sure you have their "DDL" before dropping them)
update the row(s) to fix the issue
create table tempotbl as select * from original_table
truncate original_table (only if previous step is OK)
insert into original_table select * from tempotbl;
create all indexes on original_table
drop table tempotbl

If not done yet, you might open a Service Request on "MOS" for help about root cause.

Best regards,

Bruno.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01499: table/index cross reference failure - see trace file
Author: Tso P, South Africa
Date: Feb 23, 2018, 09:19, 117 days ago
Message: Thanks Bruno

I really appreciate!!!

I have an SR with Oracle I will update the thread as what was the root cause.

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01499: table/index cross reference failure - see trace file
Author: Tso P, South Africa
Date: Feb 27, 2018, 07:47, 113 days ago
Message: Thanks everyone for the replies...

Oracle suggested this:

ALERT Bug 18607546 ORA-600 [kdblkcheckerror]..[6266] Corruption with Self-referenced Chained row. ORA-600 [kdsgrp1] / Wrong Results / ORA-8102 (Doc ID 1944645.1)

I am going to install the patch as suggested.

Thanks guys
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-01499: table/index cross reference failure - see trace file
Author: Tso P, South Africa
Date: Feb 27, 2018, 22:17, 113 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Thanks Bruno

I have being reading Tom Kyte book:

 Note There is a common misperception that ANALYZE should not be used as a command in Oracle—that the DBMS_STATS package supersedes it. This is not true. What is true is that ANALYZE should not be used to gather statistics, but the other capabilities of ANALYZE still apply. The ANALYZE command should be used to perform operations such as validating the structure of an index (as we will below) or listing chained rows in a table. DBMS_STATS should be used exclusively to gather statistics on objects.

I was actually validating the structure of an index.

Thanks a lot for your replies...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here