ORA-08103 : object no longer exists

[Former Member]

Hello all,

When BW team try to do extraction they hit error Database error text........: "ORA-08103: object no longer exists"

it occurs at table /BIC/B0000223000.

I suspect there they might be oracle block corruption as i have referred to notes Note 365481 - Block corruptions.

From Oracle, when we try to describe the table, it can display the table, but when i try to select count(*) from "/BIC/B0000223000" it prompted "ORA-08103: object no longer exists"

After referring to notes Note 23345 - Consistency check of ORACLE database, i try to run the analyze table "/BIC/B0000223000" validate structure cascade; but still it gave the same error which is "ORA-08103: object no longer exists".

After that, i try to select the index for the table, select index_name from dba_indexes where table_name = '/BIC/B0000223000', it prompted that no rows selected, meaning that the index already missing.

so, after reviewing the solution provided by notes 365481, they advise to do the index reorganization. Is it relevant for us to do the index reorganization? because the index does not exist.. Is there any suggestion or experienced the same problem? thank you.

[stefan_koehler]

Hello Salim,

please take a look at this one:

- http://ora-08103.ora-code.com/

Or you maybe hitting the 2GB+ problem... what operating system are you using and what is the limit?

For checking your oracle ddic take a look at metalink-note #136697.1

For the 2GB+ problem take a look at here:

- http://djiang.wordpress.com/2006/07/25/ora-08103/

Regards

Stefan

[Former Member]

Hello,

I noticed the problem when i try to run the SAP update optimizer statistic, and it return error when it hit the table /BIC/B0000223000~0, and it prompt that the object does not exist... then i asked the functional team to run some jobs that will hit the table... after that the error occured.

the reason that might happened here is that by the index creation that we have done before a few weeks ago and we are using the parallel 'nologging' option, just like the SAP Note 365481 mentioned.

we are using aix-unix.

[stefan_koehler]

Hello

>> and it return error when it hit the table /BIC/B0000223000~0

The object /BIC/B0000223000~0 is not a table.. it is a primary index.

>> the reason that might happened here is that by the index creation that we have done before a few weeks ago and we are using the parallel 'nologging' option, just like the SAP Note 365481 mentioned.

Ok but in this way, you had to restore and recover your database to hit this situation.

Please do the following:

> sqlplus "/ as sysdba"

> SELECT INDEX_NAME, STATUS from DBA_INDEXES WHERE INDEX_NAME = '/BIC/B0000223000~0';

If you have recovered your database and created indexes with the nologging option, you can use the BR*Tools to identify the indexes and rebuild them - take a look at sapnote #849485

Is there an entry in the ALERT.log or a trace file generated, if you hit the ORA-08103?

Regards

Stefan

[Former Member]

Hello,

Yes, actually we did restore and recover our database recently, and that is when this error came out.

I have already done the search for the index '/BIC/B0000223000~0' from dba_indexes and it return no rows selected.

Then i try to rebuild the index from sap using se14 then it prompted the error

ORA-08103: object no longer exists

DDL time(___1): ...343,768 milliseconds

The SQL statement was not executed

Index could not be created completely in the database

Index /BIC/B0000223000-0 could not be created

There is no alert log or trace file produced when it hit the error.

any idea.?

thanx for your help btw.

[stefan_koehler]

Hello Salim,

you can try to rebuild the index online.. then the corresponding data is read from the table. (for more information take a look at metalinknote #278600.1)

SE38 => RSANAORA => Enter the Indexname and choose ALTER INDEX REBUILD ONLINE

The ONLINE clause is very important in this case.

Regards

Stefan

[Former Member]

Hello,

I have tried to do the alter index "/BIC/B0000223000~0" rebuild online; via oracle or sap.... it prompted - specified index does not exist...

That is why the index cannot be found in dba_indexes in the first place.

[stefan_koehler]

Hello,

is this a partitioned index ( i am not a bw specialist, so i don't know the naming conventions)?

Please post the output of the following:

> sqlplus "/ as sysdba"

> SQL> SELECT PARTITION_NAME, STATUS from ALL_IND_PARTITIONS WHERE INDEX_NAME = '/BIC/B0000223000~0';

If yes then you have to rebuild the partitions of the index the following way:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_1008.htm#i2136031

Regards

Stefan

[Former Member]

Hello back,

The said index and table is not partitioned.

i have run the query,

SQL> SELECT PARTITION_NAME, STATUS from ALL_IND_PARTITIONS WHERE INDEX_NAME = '/BIC/B0000223000~0';

no rows selected