RE: intermittent ora 8103 errors object no longer exists
- From: Tanel Poder <tanel.poder.003@xxxxxxx>
- To: oracledbaquestions@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 29 Aug 2008 22:27:23 +0800
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;
2 /
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Dba DBA
Sent: Friday, August 29, 2008 21:59
To: oracle-l@xxxxxxxxxxxxx
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.
Other related posts:
