RE: intermittent ora 8103 errors object no longer exists

If you have cursors open since before the truncate happened, then if Oracle detects that the segment it reads is actually truncated (or dropped) then it returns this error.  

This is done by checking the data_object_id in block headers.  

So, when you truncate or drop a table, your query may continue running ok as drop/truncate leave the datablocks (along their data_object_ids) as they were.  

Truncate only increments the data object id in segment header, so if your query is lucky enough to not touch the segment header (like index range/full scans for example) it won't even realize that the table has been truncated.... until you start inserting data again, which causes old blocks to be reformatted and the error to be raised. Drop doesn't even touch the header block either! (and this is why you can drop tables from read only tablespaces - no changes to segment's datablocks are done during a drop).  

So, with index (and hash) lookups your query might run a long time without realizing that the underlying table has been truncated or dropped.  

With full segment scans you can see the error much sooner as after scanning every 10 extents (IIRC) Oracle goes back to segment header (or extent map block) to get more extent pointers. And if segment header's data object id has changed, the error is raised.  

Here's a little demo, showing that a previously opened cursor can fetch from a dropped table:    

SQL> create table t as select * from dual;  

Table created.  

SQL> var c refcursor
SQL>
SQL> begin open :c for select * from t; end;

PL/SQL procedure successfully completed.  

SQL> drop table t purge;  

Table dropped.  

SQL> print c  

D
-
X  

SQL> However if I drop the table and reuse that space (by recreating another table), then the cursor fetch will fail:  

SQL> create table t as select * from dual;  

Table created.  

SQL> begin open :c for select * from t; end;   2 /  

PL/SQL procedure successfully completed.  

SQL> drop table t purge;  

Table dropped.  

SQL> create table t(a int); -- this command will overwrite the previous table's header block  

Table created.  

SQL> print c
ERROR:
ORA-08103: object no longer exists    

no rows selected  

--

Regards,
Tanel Poder
http://blog.tanelpoder.com <http://blog.tanelpoder.com/> http://n.otepad.com <http://n.otepad.com/> - n.ote this!  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dba DBA
Sent: Friday, August 29, 2008 21:59
To: oracle-l_at_freelists.org
Subject: intermittent ora 8103 errors object no longer exists

I have functions that return ref cursors. We are getting these intermittent. The query in question is a join between 2 tables.

1. Table A:Has never changed in production it is 100% static
2. Table B: Gets truncated and reloaded once a day.

I am testing the query out of sqlplus and others are testing it from java so

select package.function(variables)
from dual;

1. Some times it returns data.
2. some times we get ora-8103 to start the query
3. some times we return data and then get ora 8103

I personally have not been able to re-create it. But i can go to someone else's laptop and they run the function with the same variables and get the errors.
I googled this and saw
1. issues with global temp tables. We are not using global temp tables. Just 2 heap tables
2. data gets changed/truncated, etc... during query. No the data is stagnat 3. We do have logging procedures that run before and after the ref cursor parser. They insert to a table from an autonomous transaction. We have never had a problem with this and use this in alot of places.

I am a bit lost on something I can't re-create. None of things on the web or asktom seem to point to my issue since we are basically dealing with static data. None of the data is changing while we are running the queries.

--

http://www.freelists.org/webpage/oracle-l Received on Fri Aug 29 2008 - 09:27:23 CDT