You Asked
Hi, Tom, I do two tests about cursor in ORACLE 19.3C. In test1 why print "x" is normal but print "y" is abnormal ? In test2 why print "y" run half of the way ? I get information from:
https://asktom.oracle.com/pls/apex/asktom.search?tag=ora-08103object-no-longer-exists [Test1]SQL> select count(*) from dba_objects;
COUNT(*)
----------
13770
create table tx as select * from dba_objects where rownum < 11;
create table ty as select * from dba_objects;
variable x refcursor
variable y refcursor
declare
begin
open :x for select * from tx;
open :y for select * from ty;
end;
/
drop table tx;
drop table ty;
SQL> print x
OWNER OBJECT_NAME ....
--------- ----------------
SYS ICOL$
SYS I_USER1
SYS CON$
SYS UNDO$
SYS C_COBJ#
SYS I_OBJ#
SYS PROXY_ROLE_DATA$
SYS I_IND1
SYS I_CDEF2
SYS I_OBJ5
10 rows selected.
SQL> print y
ERROR:
ORA-08103: object no longer exists
no rows selected[Test2]create table tx as select * from dba_objects where rownum < 11;
create table ty as select * from dba_objects;
variable x refcursor
variable y refcursor
declare
nomeaning tx%rowtype;
begin
open :x for select * from tx;
open :y for select * from ty;
fetch :y into nomeaning;
end;
/
drop table tx;
drop table ty;
SQL> print x
OWNER OBJECT_NAME ....
--------- ----------------
SYS ICOL$
SYS I_USER1
SYS CON$
SYS UNDO$
SYS C_COBJ#
SYS I_OBJ#
SYS PROXY_ROLE_DATA$
SYS I_IND1
SYS I_CDEF2
SYS I_OBJ5
10 rows selected.
SQL> print y
OWNER OBJECT_NAME ....
--------- ----------------
... ...
SYS SYS_LOB0000006212C00003$$
ERROR:
ORA-08103: object no longer exists
6210 rows selected.
and Connor said...
When you start fetching from a cursor, we're doing a few reads
a- the segment header block ( to get the first few extents)
b- any extent map blocks (for the additional extents)
c- then the extents themselves (for the data)
If all of those things are available (after the object has been dropped) then it is likely that a query that was initiated before the table was dropped will still succeed.
If any of those things can no longer be read/found, then you'll get ORA-8103.
The larger the object, the more chances you need to be "lucky" to have all that stuff still existing. For a small table, the more chances its all in the buffer cache and we don't even need to do any additional lookups to find the data.
Is this answer out of date? If it is, please let us know via a Comment