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.