Home » RDBMS Server » Server Administration » ORA-01499 (Oracle 9204, XP)
ORA-01499 [message #417768] Tue, 11 August 2009 08:28 Go to next message
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..

Re: ORA-01499 [message #417770 is a reply to message #417768] Tue, 11 August 2009 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Drop and recreate the index.

Regards
Michel
Re: ORA-01499 [message #417950 is a reply to message #417770] Wed, 12 August 2009 04:43 Go to previous messageGo to next message
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)


Re: ORA-01499 [message #417964 is a reply to message #417950] Wed, 12 August 2009 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
Re: ORA-01499 [message #417973 is a reply to message #417964] Wed, 12 August 2009 05:47 Go to previous messageGo to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Did that already,

thank you
Re: ORA-01499 [message #417975 is a reply to message #417973] Wed, 12 August 2009 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So?

Regards
Michel
Re: ORA-01499 [message #417979 is a reply to message #417975] Wed, 12 August 2009 05:54 Go to previous messageGo to next message
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..
Re: ORA-01499 [message #417981 is a reply to message #417979] Wed, 12 August 2009 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Most often too much maintenance (like rebuilding online) on indexes.

Regards
Michel
Re: ORA-01499 [message #418189 is a reply to message #417981] Thu, 13 August 2009 05:38 Go to previous messageGo to next message
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


Re: ORA-01499 [message #418190 is a reply to message #417768] Thu, 13 August 2009 05:52 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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?
Re: ORA-01499 [message #418193 is a reply to message #418189] Thu, 13 August 2009 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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
Re: ORA-01499 [message #418508 is a reply to message #417768] Sun, 16 August 2009 14:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: ORA-01499 [message #418893 is a reply to message #418836] Wed, 19 August 2009 01:45 Go to previous messageGo to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Hi,

the schedulers are giving errors

ORA-01555


The undotablespace is 15gb and

undo_retention = 3600



i am not sure wheather is this related to ora-01499 issue.
Re: ORA-01499 [message #418903 is a reply to message #418893] Wed, 19 August 2009 02:22 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So analyze what you've done when errors come.

Regards
Michel
Previous Topic: stack space in shared server configuration
Next Topic: ORA-03113: end-of-file on communication channel (merged)
Goto Forum:
  


Current Time: Sun Dec 04 00:46:31 CST 2016

Total time taken to generate the page: 0.07755 seconds