ORA-08103: object no longer exists
Author JP Vijaykumar Oracle DBA
Date March 8th 2017
Modified May 14th 2017
Modified July 7th 2017

Recently, I worked on an issue - while executing a select on a view,

select count(*) from temp_jp_vw where companion_id in ('VEEKSHA','SAKETH');

it failed with error "ORA-08103: object no longer exists"

Performed troubleshooting with no luck.

In the meantime, I researched on a work around for the issue.

Every environment is unique. In our environment, for the above issue, the following
work arounds worked successfully without the error ORA-8103.
These work arounds may or may not work in other environments.
Readers' descretion/research is advised.

01) --USE PARALLEL HINT IN THE SELECT QUERY
select /*+ parallel(a 2)*/ count(*) from temp_jp_vw a where companion_id in ('VEEKSHA','SAKETH');


COUNT(*)
----------
44036

Elapsed: 00:01:42.67

02) --THE VIEW WAS CREATED ON 4 TABLES + ONE VIEW
--ALTER ALL BASE TABLES' DEGREE OF PARALLELISM TO 2
OR SET ANY ONE BASE TABLE'S DEGREE OF PARALLELISM TO 2
& THE QUERY WORKS.

select 'alter table '||table_name||' parallel(degree 2);' from user_tables
where table_name in (select REFERENCED_NAME from (select * from dba_dependencies
where name in ('TEMP_JP_VW' ) and type='VIEW'));

'ALTERTABLE'||TABLE_NAME||'PARALLEL(DEGREE2);'
--------------------------------------------------------------
alter table ........... parallel(degree 2);
alter table ........... parallel(degree 2);
alter table ........... parallel(degree 2);
alter table ........... parallel(degree 2);

set timing on
select count(*) from temp_jp_vw where companion_id in ('VEEKSHA','SAKETH');


COUNT(*)
----------
44036

Elapsed: 00:01:46.34

03) --RE-WRITE THE VIEW'S DEFINITION, TUNE TABLE'S JOIN ORDER
--LONG TERM SOLUTION.

04) --SINGLE COLUMN BITMAP INDEXES EXISTED ON THE BASE TABLES OF THE VIEW.

IN THE ORIGINAL QUERY, THE FOLLOWING OPERATION IS OCCURING BETWEEN TWO BITMAP INDEXES.

"BITMAP INDEX SINGLE VALUE"

TO AVOID THE ABOVE OPERATION,ALTERED TWO BITMAP INDEXES ON ONE TABLE AS UNUSABLE,
THE QUERY EXECUTED SUCCESSFULLY WITHOUT USING ANY HINT.

SQL> alter index .............. unusable;

Index altered.

Elapsed: 00:00:00.22
SQL> alter index ............... unusable;

Index altered.

Elapsed: 00:00:00.21
SQL> set timing on
SQL> select count(*) from temp_jp_vw where company_cd in ('VEEKSHA','SAKETH');

COUNT(*)
----------
44036

05)
--INVISIBLE FEATURE IS VERY USEFUL IN OUR SCENARIO. THE INDEX WILL NOT BE
USED BY THE SQL, JUST ALTER THE INDEX AND MAKE IT INVISIBLE/VISIBLE.


SQL> select index_name,visibility from user_indexes where index_name='...............';

INDEX_NAME VISIBILIT
------------------------------ ---------
....................... INVISIBLE

SQL> alter index .................... invisible;

Index altered.

Elapsed: 00:00:00.07
SQL> alter index .................... visible;

Index altered.

Elapsed: 00:00:00.02
SQL> alter index ...................... invisible;

Index altered.

Elapsed: 00:00:00.01
SQL> select count(*) from temp_jp_vw where company_cd in ('VEEKSHA','SAKETH');


COUNT(*)
----------
44036

Elapsed: 00:02:43.82


06) --TWO BITMAP INDEXES ON ONE TABLE WERE MERGED WITH THE OPERATION
"BITMAP INDEX SINGLE VALUE", DROPPED THE TWO BITMAP INDEXES AND
CREATED ONE BTREE INDEX ON THESE TWO COLUMNS. THE QUERY EXECUTED SUCCESSFULLY.


SQL> drop index ...............;

Index dropped.

Elapsed: 00:00:00.25
SQL> drop index ..................;

Elapsed: 00:00:00.00
SQL> create index ...................0710 on
....................(............_NO,............._IND)
tablespace ..._d01;

Index created.

Elapsed: 00:00:33.60
SQL> select count(*) from temp_jp_vw where company_cd in ('VEEKSHA','SAKETH');

COUNT(*)
----------
44036

Elapsed: 00:02:16.53

--------------------------------------------------
References:
Metalink ID# 8103.1 --Troubleshooting the issue
Metalink ID 17204397.8 --How the error occurs
https://translate.google.com/translate?hl=en&sl=zh-CN&u=http://www.parnassusdata.com/en/node/669%3Flanguage%3Den&prev=search
https://translate.google.com/translate?hl=en&sl=ru&u=http://my-oracle.it-blogs.com.ua/post-510.aspx&prev=search
https://translate.google.com/translate?hl=en&sl=ru&u=http://my-oracle.it-blogs.com.ua/post-510.aspx&prev=search --analyze table validate structure/dbv
https://hourim.wordpress.com/2013/03/09/sql-patch-and-sql-plan-baseline-how-do-they-collaborate/
--USE SQL PATCH TO FORCE PARALLEL HINT FOR A SPECIFIC SQL QUERY'S EXECUTON "select * from dba_sql_patches;"