Skip to Main Content
  • Questions
  • Large table and small table behave differently when defining cursors

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: May 26, 2023 - 7:40 am UTC

Last updated: June 02, 2023 - 3:03 am UTC

Version: 19.3c

Viewed 1000+ times

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

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.