Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, Arun.

Asked: September 15, 2015 - 8:11 pm UTC

Last updated: June 03, 2019 - 8:45 am UTC


Viewed 100K+ times! This question is

You Asked


I am getting an error Ora-08103: Object No longer exists when select queries are fired against a partitioned table having local Bit map indexes. But when the same process/job (which fires select query) is restarted the issue does not come up again. It magically vanishes.

Let me explain the design and probably you can suggest the probable reasons for getting the issue.

I am using exchange partition to load a partitioned table and in parallel there are select queries running on the same partitioned table.

Exchange partition process followed is as below:
1. Create non partition table similar to partitioned table using CTAS
2. Load the non partitioned table
3. Exchange the partition excluding Indexes
4. Rebuild Local Bit map Indexes on the partition
5. Drop non partitioned table

While the above process is happening, there are some select queries being fired on the partitioned table which might do a full table scan of the partitioned table.

Please can you help me in finding out why would the select queries throw Ora:08103 error and resolution for the same.


and Connor said...

Here's a good example (not using partitions but illustrates the point)

SQL> variable x refcursor
SQL> variable y refcursor
SQL> declare
  2          l_table_name t.table_name%type;
  3  begin
  4          open :x for select * from t;
  5          open :y for select * from t;
  7          fetch :y into l_table_name;
  8  end;
  9  /
PL/SQL procedure successfully completed.

both queries are the "same", the only difference being we fetched the first row from :y

SQL> truncate table t;
Table truncated.
SQL> print x
ORA-08103: object no longer exists
no rows selected

:x doesn't know what data to read but...

SQL> print y
9 rows selected.

:y did

Because 'y' had started, we knew all the extents etc we would need to read so our query could proceed. 'x' had not yet done that, and then re truncated and we got in a mess.

So back to your specific question. If at the time your query commences, we can obtain enough information to work out where we'll need to go in the database to find all your data, then there is a *chance* that our query will run through to completion.

It is NOT a guarantee however. Going back to the example above - even though 'y' is working, if whilst that query was fetching, someone came along and created a new table - it might use the SAME space that our table "T" was using before we truncated. In that case, the 'y' query would fail half way through with ora-8103

In your case, try deferring the drop of the non-partitioned table (because it might still be in active use by a running query).

Hope this helps.


  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment


Arun Bhosle, September 16, 2015 - 7:17 am UTC

Very helpful!

Jayson Magnus, May 09, 2016 - 7:15 pm UTC

This is a great simple example.

I used something similar to show how a procedure could get a ORA-08103 (object no longer exists) if while it is firing a drop/add partition script was running on the same objects.

Thank you.
Chris Saxon
May 10, 2016 - 1:01 am UTC

Yes, partition maintenance is probably the most common time you see this error.

Ghassan, May 10, 2016 - 7:38 am UTC

The exchange partition will make source Truncated. So if another query was triggered it cannot succeed since the data are now missing from that source.
This join similar explanation of above.
Connor McDonald
May 11, 2016 - 1:06 am UTC

Where things can get interesting is that a query that has already commenced against an exchanged partition *may* still succeed (as long as nothing messed with the original data).

For example:

SQL> create table t ( x int, y char(100))
  2  partition by list ( x )
  3  ( partition p1 values (1),
  4    partition p2 values (2),
  5    partition p3 values (3),
  6    partition p4 values (4)
  7  );

Table created.

SQL> insert /*+ APPEND */ into t
  2  select c1, lpad(rownum,10)
  3  from
  4    ( select rownum c1 from dual connect by level <= 4 ),
  5    ( select 1 from dual connect by level <= 2000 ),
  6    ( select 1 from dual connect by level <= 2000 );

16000000 rows created.

SQL> commit;

Commit complete.

SQL> create table t_ex ( x int, y char(100));

Table created.

SQL> select max(y) from t;
(still running...)

-- in another session I do this:

SQL> alter table t exchange partition p4 with table t_ex without validation;

Table altered.

-- now back to session 1


You can see that even though an exchange took place, we still located the object and completed the query. Now let's do that again, but what if that object is no longer available

SQL> select max(y) from t;
(still running...)

-- in another session I do this:

SQL> alter table t exchange partition p3 with table t_ex without validation;

Table altered.

SQL> truncate table t_ex;

Table truncated.

-- now back to session 1

select max(y) from t
ERROR at line 1:
ORA-08103: object no longer exists

That's why people often describe this error as intermittent.


Arun, June 07, 2016 - 6:01 am UTC

My understanding is that if the select query is made to access partitions 1 to 3 through partition pruning and exchange and truncate is happening on partition 4 then the issue should not come up as partitions used in select are different then the partition being been altered.

Is it correct?
Connor McDonald
June 07, 2016 - 7:19 am UTC

As long as we've made the decision to "not" read partition 4 before the truncate occurs, then you should be OK.

But that's taking a fairly risky approach. Even something like dynamic sampling during optimization *might* visit partition 4.

One doubt

mani, July 17, 2016 - 11:02 am UTC

Hi Tom,

I recently faced this error in production and am not able to replicate this scenario. There is a file that is read by a procedure to load into the tables by validating them by business logic. For example,the file has 8000 records, the procedure reads 5000 records and throws this error "object no longer exists" and ignore the rest 3000 records.

There are no partitions on the table. The version is 12c, let me know if you have any idea on what to check to find root cause.
Connor McDonald
July 18, 2016 - 12:10 am UTC

Partitioned tables are the most *common* scenario to see this error, but by no means the only way.

Anything that physically alters an underlying segment that is being queried can create the issue. Examples:

- truncate table
- alter table move
- alter index rebuild

going on whilst someone is using that same segment for a query

8103 steps to handle

david scott, September 12, 2016 - 2:38 pm UTC

I have been reading quite a few descriptions of what causes 8103, and the information is good, no question. But I am not seeing solutions, as in "do this, and this issue goes away". So I am wondering - can this addressed by something as simple as maybe performing my TRUNCATE in one session, and my INSERT in another? (My case is a TRUNCATE action in a stored procedure, which appears to work, and next a busy INSERT INTO with multiple levels of data collection in it, no indexes and no partitions).

8103 solutions

david scott, September 12, 2016 - 2:59 pm UTC

Apologies for a 2nd entry before letting you see the 1st, but I meant to convey that doing my TRUNCATE in one session would be ahead of doing my INSERT INTO in another session. Also, I wish to ask on this: is the exception actually pointing to the object with the issue, or might this arise out of some object deeper in the inline view, and the exception just trickles up to the main table receiving the INSERT?
Chris Saxon
September 13, 2016 - 2:18 am UTC

You've basically got a contention issue - two people wanting to do things against the same segment - one wants to read, one wants to (for example) erase.

So (as with any contention) you want to:

- minimize the time frame at which it is possible
- serialize access if necessary

For example, your truncate activity could be done in quietest possible periods. Or mimic'd with exchange partition (ie with an empty table) to reduce the likelihood that the segment space is reclaimed by something else.

Or truncate operations could scan active running sql's to try avoid running during a potential problem timeframe.

In extreme cases, you could have sessions take a shared lock (via dbms_lock) in order to query the object, and the truncate operation takes an exclusive lock on the same lockid, to totally force separation.

Scenarios in which it happens on partitions

Jo Desmet, December 19, 2018 - 12:16 am UTC

I am curious about he exact scenario's where it might happen, affecting partitions particularly.

I was expecting that when one partition is altered, it would not affect SELECT from another partition. However to my surprise it did fail with the same error.

This was executed using an ALTER TABLE SWAP PARTITION - again on the partition where I was NOT selecting from.
Connor McDonald
December 19, 2018 - 2:00 am UTC

"select from another partition" does not necessarily mean we *only* accessed that partition during the execution of the query.

We'd need to something a little more concrete.

Another possible cause

Norman Dunbar, June 03, 2019 - 8:43 am UTC

Morning All,

this is not partition related, but did cause me a little head scratching recently.

A system uses DBMS_CDC to move changes to another database. That other database is now moving to the cloud. (Sigh!) The network is a lot slower but a new subscriber was set up to copy the data to the cloud for parallel runs with the existing setup.

Because the network is slower, jobs take longer and the automatic purge job comes along and does the purge, resulting in the clod data transfer jobs getting this error.

We stopped the purge job and all is well. Other than the cloud and the network of course! :o(

Connor McDonald
June 03, 2019 - 8:45 am UTC

Purging tends to do that :-)

Thanks for stopping by Norman

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library