Getting ORA-8103 When Reading from a Cursor

by Granville Bonyata on December 16, 2012

I came across this when using Oracle’s partition exchange. After doing the partition exchange, I truncated the data from the now unnecessary table. However, we found in the application logs that at that time, an ORA-8103 was raised when reading from a cursor.

ORA-08103 is “object no longer exists” which doesn’t seem to fit the situation.

Partition exchange is designed so it can be done while the DB is online. You have a table that looks identical to the partition you want to replace. You issue the exchange command and Oracle updates the data dictionary so that the table is now the partition, and the partition has been relabeled as the table. Any queries running against the partition will continue running against the object even though it has been re-identified as a table.

One would thing the worst that could happen by truncating the data is that the query comes up empty handed. But instead, we get the 8103 error.

It turns out this has nothing to do with partition exchange and everything to do with opening a cursor, truncating the table in a separate session, and then reading from the cursor. Here’s an example:

DROP TABLE my_tables;

CREATE TABLE my_tables AS SELECT * FROM user_tables;

DECLARE

o_cursor SYS_REFCURSOR;

v_table_name VARCHAR2(1000);
v_job NUMBER;

CURSOR c1 IS
SELECT table_name
FROM my_tables;

BEGIN

OPEN c1;

--Use a job so the truncate runs as another session. It errors either way, but this illustrates the
--usual circumstance of when this error is caused by truncating a table.
DBMS_JOB.SUBMIT(v_job,'BEGIN EXECUTE IMMEDIATE ''TRUNCATE TABLE my_tables''; END;');

DBMS_LOCK.SLEEP(22); --Give the job time to run.

FETCH c1 INTO v_table_name;

DBMS_OUTPUT.PUT_LINE('Name: '||v_table_name);
close c1;

END;
/

In my case, the fix was simple – Add a SLEEP statement to give the old queries time enough to complete before truncating the table.

Previous post:

Next post: