Re: intermittent ora 8103 errors object no longer exists

The ref cursor is in a package. How do you close a ref cursor if you want to return it to a java user? It doesn't really go out of scope. does it stay open as long as the session is open? So if the they are using middle tier connection pooling and have say 10 sessions open at all times then the ref cursor never closes.

We have been truncating and rebuilding one of the tables for several weeks and this just occurred now.

On Fri, Aug 29, 2008 at 10:27 AM, Tanel Poder <tanel.poder.003_at_mail.ee>wrote:

> 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://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