OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » ORA-01499 (Oracle 9204, XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01499 [message #417768]

Tue, 11 August 2009 08:28

saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE

Member

Hi Experts,

I am facing a table/index cross refrence issue

SQL> analyze table atlas.DSL_ITEMEFFORTDATA VALIDATE STRUCTURE CASCADE ONLINE;
analyze table atlas.DSL_ITEMEFFORTDATA VALIDATE STRUCTURE CASCADE ONLINE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

I will upload the concerned trace file.
Please suggest on the solution..

Attachment: kcatlas_ora_4132.txt
(Size: 46.65KB, Downloaded 1295 times)

Report message to a moderator

Re: ORA-01499 [message #417770 is a reply to message #417768]

Tue, 11 August 2009 08:32

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Drop and recreate the index.

Regards
Michel

Report message to a moderator

Re: ORA-01499 [message #417950 is a reply to message #417770]

Wed, 12 August 2009 04:43

saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE

Member

Thank You Michel,

I dropped and recreated the index for that table.
But still some of the Procedures are giving the error

ORA-08102: index key not found, obj# 40271, dba 83918039 (2)

Report message to a moderator

Re: ORA-01499 [message #417964 is a reply to message #417950]

Wed, 12 August 2009 05:28

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

ORA-08102: index key not found, obj# %s, file %s, block %s (%s)
 *Cause:  Internal error: possible inconsistency in index
 *Action:  Send trace file to your customer support representative, along
             with information on reproducing the error

Regards
Michel

Report message to a moderator

Re: ORA-01499 [message #417973 is a reply to message #417964]

Wed, 12 August 2009 05:47

saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE

Member

Did that already,

thank you

Report message to a moderator

Re: ORA-01499 [message #417975 is a reply to message #417973]

Wed, 12 August 2009 05:51

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So?

Regards
Michel

Report message to a moderator

Re: ORA-01499 [message #417979 is a reply to message #417975]

Wed, 12 August 2009 05:54

saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE

Member

I have raised an SR with ORACLE and have been co ordinating with them,

We have identified certain tables which are the cause.

Dropping and Recreation of indexes has been done on those tables.
We are further monitoring for any other Procedure giving this error.

I wnated to understand the main cause of it..

Report message to a moderator

Re: ORA-01499 [message #417981 is a reply to message #417979]

Wed, 12 August 2009 05:58

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Most often too much maintenance (like rebuilding online) on indexes.

Regards
Michel

Report message to a moderator

Re: ORA-01499 [message #418189 is a reply to message #417981]

Thu, 13 August 2009 05:38

saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE

Member

Thanks Michel,

We have an index

ATLAS.SYS_C0020913

below is the script for it

CREATE UNIQUE INDEX ATLAS.SYS_C0020913 ON ATLAS.METRIC_HISTORY_VALUES(OWNERTYPE, OWNERID, ITEMTYPE, ITEMID, METRICVALUENAME, STARTDATE)   PCTFREE 10 INITRANS 2 MAXTRANS 255
            STORAGE(
                        INITIAL 64 K
                        NEXT 0 K
                        MINEXTENTS 1
                        MAXEXTENTS UNLIMITED
                        PCTINCREASE 0
                        BUFFER_POOL DEFAULT
                        )
NOLOGGING   TABLESPACE DIGITE_INDEX

A Procedure using this table

METRIC_HISTORY_VALUES

is giving errors.
Can we drop and recreate this index, i was doubtful since this is a SYSTEM INDEX

Report message to a moderator

Re: ORA-01499 [message #418190 is a reply to message #417768]

Thu, 13 August 2009 05:52

cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester

Senior Member

If you need to drop and recreate a unique index then you need to take the system offline to do so.
Otherwise you run the serious risk of getting duplicate records.

What do you mean by SYSTEM INDEX?

Report message to a moderator

Re: ORA-01499 [message #418193 is a reply to message #418189]

Thu, 13 August 2009 06:01

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Drop and recreate the associated unique or primary constraint.
Of course stop the applications when you proceed.

Regards
Michel

Report message to a moderator

Re: ORA-01499 [message #418508 is a reply to message #417768]

Sun, 16 August 2009 14:52

quaronn321
Messages: 2
Registered: August 2009

Junior Member

Dropping and Recreation of indexes has been done on those tables.
We are further monitoring for any other Procedure giving this error.

[Updated on: Mon, 17 August 2009 12:39]

Report message to a moderator

Re: ORA-01499 [message #418836 is a reply to message #417768]

Tue, 18 August 2009 13:05

quaronn321
Messages: 2
Registered: August 2009

Junior Member

We have identified certain tables which are the cause.

Dropping and Recreation of indexes has been done on those tables.
We are further monitoring for any other Procedure giving this error.

[Updated on: Tue, 18 August 2009 13:05]

Report message to a moderator