Skip to Main Content
  • Questions
  • Read consistency accross cursors in one procedure

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sean.

Asked: January 24, 2003 - 6:29 pm UTC

Last updated: November 24, 2008 - 11:00 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

I am looking for read consistency across multiple cursors in a packaged procedure. In the past I have opened the cursors that I wanted to be consistent at the start of the procedure, used them, and closed them at the end. I am starting to think that the time the first cursor takes to open, and resolve it's result set is making the subsequent cursor inconsistent, although this seems to have worked 99% of the time.

Example:

DECLARE
CURSOR Cur1 IS
SELECT SUM(X) FROM A WHERE SummaryFlag = 'N';
CURSOR Cur2 IS
SELECT ROWID FROM A WHERE SummaryFlag = 'N';
BEGIN
OPEN Cur1;
OPEN Cur2;
.
FOR Rows IN Cur1
UPDATE ASummary
.
.
FOR Rows IN Cur2
UPDATE A SET SummaryFlag = 'Y' WHERE RowId = Cur2.ROWID;

I have had a few occasions where the summary table does not contain the information that has now been flagged as summarized.

Does opening the cursors one right after the other guarantee a consistent result set, and if not why? Will using "ALTER TRANSACTION ISOLATION LEVEL SERIALIZABLE" fix this? How can I set my ISOLATION LEVEL and ROLLBACK segment at the same time?

Thanks in advance.

and Tom said...

serializable is what you are looking for -- I would say that picking a rollback segment is a terrible thing to do (hey, I have said that! even put it in writing in my book). So, here are my suggestions:


1) So, one solution would be to just set serializable and erase all lines of code that specify a rollback segments. You should do this regardless, just a bad practice...


2) Another would be to use an autonomous transaction to open the result sets at point "T" in time. Consider:

ops$tkyte@ORA817DEV> create or replace package demo_pkg
2 as
3
4 procedure do_stuff;
5
6 end;
7 /

Package created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> drop table t;

Table dropped.

ops$tkyte@ORA817DEV> create table t ( x int, summaryFlag char(1) );

Table created.

ops$tkyte@ORA817DEV> insert into t select rownum, 'N' from all_users where rownum < 10;

9 rows created.

so, we have nine rows in there...

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace package body demo_pkg
2 as
3
4 cursor c1 is select sum(x) from t where summaryFlag = 'N';
5 cursor c2 is select rowid from t where summaryFlag = 'N';
6
7 procedure slip_some_in
8 is
9 pragma autonomous_transaction;
10 begin
11 insert into t select rownum*10000, 'N' from all_users;
12 commit;
13 end;
14
15
16 procedure open_cursors
17 is
18 pragma autonomous_transaction;
19 begin
20 execute immediate 'set transaction isolation level serializable';
21 open c1;
22 slip_some_in;
23 open c2;
24 commit;
25 end;

note how I slipped new rows in there in between the two opens -- in another transaction tho, since we are serializable IN THIS OPEN code only -- we won't be able to see those changes


26
27
28 procedure do_stuff
29 is
30 l_sum number;
31 l_rowid rowid;
32 begin
33 open_cursors;
34
35 loop
36 fetch c1 into l_sum;
37 exit when c1%notfound;
38 dbms_output.put_line( 'sum = ' || l_sum );
39 end loop;
40 close c1;
41
42 loop
43 fetch c2 into l_rowid;
44 exit when c2%notfound;
45 dbms_output.put_line( 'rowid = ' || l_rowid );
46 end loop;
47 close c2;
48 end;
49
50 end;
51 /

Package body created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> exec demo_pkg.do_stuff
sum = 45
rowid = AAAMhOAAHAAAk3KAAA
rowid = AAAMhOAAHAAAk3KAAB
rowid = AAAMhOAAHAAAk3KAAC
rowid = AAAMhOAAHAAAk3KAAD
rowid = AAAMhOAAHAAAk3KAAE
rowid = AAAMhOAAHAAAk3KAAF
rowid = AAAMhOAAHAAAk3KAAG
rowid = AAAMhOAAHAAAk3KAAH
rowid = AAAMhOAAHAAAk3KAAI

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV> select count(*) from t;

COUNT(*)
----------
61

so the cursors, opened as of the same points in time, see consistent results


3) use materialized views to maintain these summaries and erase ALL of your code :) That is what materialized views do -- no more flags, no more code, just let the database do what the database does....

Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Read Consistency in cursor_for_loop

Js, March 15, 2004 - 4:09 am UTC

Hi,

         Using Redhat 8.0 + Oracle 9.2.0
         -------------------------------

Ref    :   "Expert one-on-one" 
Topic  : Multi-versioning + Read Consistent Queries

Chapter 1  page no. 34


 "Bear in mind that oracle does not copy the data anywhere when you
  open a cursor"

 I was trying to understand how oracle multi-versioning scheme works
 and gives the consistent result using rollback segment if required.


 

SQL> set serveroutput on
SQL>
SQL> create table a as select * from dict where
  2  rownum < 11;

Table created.

SQL> var x refcursor
SQL>
SQL> begin
  2    open :x for select * from a;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> truncate table a;

Table truncated.

SQL> print x
ERROR:
ORA-08103: object no longer exists



no rows selected


          ### Because we didn't genrate the redo...



SQL>
SQL> drop table a;

Table dropped.

SQL> create table a as select * from dict where
  2  rownum < 11;

Table created.

SQL> begin
  2       for i in ( select * from a ) loop
  3
  4         dbms_output.put_line(' -->> '||i.table_name);
  5         execute immediate ( ' Truncate table a ');
  6      end loop;
  7  end;
  8  /
-->> ALL_ALL_TABLES
-->> ALL_APPLY
-->> ALL_APPLY_CONFLICT_COLUMNS
-->> ALL_APPLY_DML_HANDLERS
-->> ALL_APPLY_ERROR
-->> ALL_APPLY_KEY_COLUMNS
-->> ALL_APPLY_PARAMETERS
-->> ALL_APPLY_PROGRESS
-->> ALL_ARGUMENTS
-->> ALL_ASSOCIATIONS

PL/SQL procedure successfully completed.

SQL> select count(*) from a;

  COUNT(*)
----------
         0

I was expecting the same result but .. ?? 

Sir, Is there any diff.in working logic 
     between Ref_cursor or this Cursor_for_loop ?

Thanks,
Js




 

Tom Kyte
March 15, 2004 - 7:36 am UTC

(because you didn't generate the UNDO -- not redo -- but anyway...)


It isn't "ref cursor vs normal cursor" it is

compare a cursor that has been parsed but has not yet touched ANYTHING (including the segment header to figure out where the table data resides)

to 

a cursor that has been parsed and has in fact read the segment header and knows what data to full scan.


You see, when you truncate or drop a table -- queries that are already accessing the data may well succeed (as your second case did) since they are reading the data that is still there (truncate doesn't zero out the data or anything, just deallocates it).  As long as the data exists, the queries will run.  but your :x result set never got started.  truncate DID zero out the storage allocation hence when :x went for the first row -- it failed. 

Consider:

ops$tkyte@ORA9IR2> create table t as select table_name from dict where rownum <11;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable x refcursor
ops$tkyte@ORA9IR2> variable y refcursor
ops$tkyte@ORA9IR2> declare
  2          l_table_name t.table_name%type;
  3  begin
  4          open :x for select * from t;
  5          open :y for select * from t;
  6
  7          fetch :y into l_table_name;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
<b>both queries are the "same".  we fetched the first row from :y however (much like you did in your second example, you fetched a row AND THEN truncated)</b>


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> truncate table t;
 
Table truncated.
 
ops$tkyte@ORA9IR2> print x
ERROR:
ORA-08103: object no longer exists
 
 
 
no rows selected

<b>:x doesn't know what data to read but...</b>
 
ops$tkyte@ORA9IR2> print y
 
TABLE_NAME
------------------------------
ALL_APPLY
ALL_APPLY_CONFLICT_COLUMNS
ALL_APPLY_DML_HANDLERS
ALL_APPLY_ERROR
ALL_APPLY_KEY_COLUMNS
ALL_APPLY_PARAMETERS
ALL_APPLY_PROGRESS
ALL_ARGUMENTS
ALL_ASSOCIATIONS
 
9 rows selected.

<b>:y did</b>

Nothing to do with multi-versioning here, it is all about "did we know what extents to read or not".  :x did not, "for x in" did.  

Now, a long running query against a table that was truncated or dropped could fail partway through if someone comes in and reused the space allocated to the object as well!!  it is not assured that :y would succeed - only because it is really small and the database is "single user" so the space would not be reused did it work this time. 

Re:

Js, March 16, 2004 - 12:26 am UTC

Wow ... ********

Good .. Explaination.

Thanks Sir ...


How about partitions

Lakshminarasimhan R, July 22, 2004 - 9:43 pm UTC

Tom

If i have a table with Two partitions A & B

One session is Truncating the Parition A

Another session is issued a long running query for retreiving values in partition B.

What is the proximity of getting

ORA-08103: object no longer exists

Does it occurs or Since it is different partition , it wont

Regards

Tom Kyte
July 23, 2004 - 8:27 am UTC

as long as the query was able to get partition elimination to kick in, you should not recieve that error as you'll never touch partition a.

Concerning read-consistency...

Laly, August 17, 2004 - 2:59 am UTC

Dear Tom,

I have a question concerning read-consistency and cursors. It is about the same example p33-34 of your book Expert one-on-one : 

SQL> create table laly as select * from all_objects;

Table created.

SQL> variable rc refcursor;
SQL> begin
  2  open :rc for select * from laly;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> delete laly;

3566 rows deleted.

SQL> commit;

Commit complete.

SQL> print rc;

OWNER                          OBJECT_NAME                                      
------------------------------ ------------------------------                   
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE            
------------------------------ ---------- -------------- ------------------     
CREATED  LAST_DDL TIMESTAMP           STATUS  T G S                             
-------- -------- ------------------- ------- - - -                             
SYS                            FILE$                                            
                                       17             17 TABLE                  
07/03/01 07/03/01 2001-03-07:17:55:02 VALID   N N N                             
                                                                                
...





I understood that when we delete rows, they are moved to rollback segment and when we print the cursor, it is rebuilding rows from the rollback segments.

Instead of deleting the rows, I dropped the table but the printing of the cursor is still working : 

SQL> create table laly as select * from all_objects;

Table created.

SQL> select count(*) from laly;

  COUNT(*)                                                                      
----------                                                                      
      3566                                                                      

SQL> variable rc refcursor;
SQL> begin
  2  open :rc for select * from laly;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> drop table laly;

Table dropped.

SQL> print rc;

OWNER                          OBJECT_NAME                                      
------------------------------ ------------------------------                   
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE            
------------------------------ ---------- -------------- ------------------     
CREATED  LAST_DDL TIMESTAMP           STATUS  T G S                             
-------- -------- ------------------- ------- - - -                             
SYS                            FILE$                                            
                                       17             17 TABLE                  
07/03/01 07/03/01 2001-03-07:17:55:02 VALID   N N N                             
                                                                                
...

SQL> select count(*) from laly;
select count(*) from laly
                     *
ERROR at line 1:
ORA-00942: table or view does not exist 

My questions :
1. shouldn't there be a lock preventing me from dropping the table when there is an opened cursor based on it
2. from where is the cursor getting the rows as for me the drop table moved nothing at all to the rollback segments.


Best regards,

Laly.

 

Tom Kyte
August 17, 2004 - 7:59 am UTC

1) nope (obviously). if you had *modified* the object, that would be a horse of a different color.

2) it is reading them right from where they used to be. a drop table doesn't rewrite the object. we got everything we needed at parse time to complete the query.

that query would easily get an ora-8031 if you did something to cause the table data to be overwritten however.

some additional questions...

Laly, August 17, 2004 - 9:37 am UTC

about 1) i was meaning not DML locks but something like a shared DDL locks to prevent a drop from underlying tables when we have a cursor
about 2) it is true : when i do truncate table... reuse storage i get my data, and when i do truncate table ... drop storage i can't get it, i'm getting ORA-08103: object no longer exists !


I don't know I feel little unwell with those queries, I feel it is an unpleasant side effect.


Tom Kyte
August 17, 2004 - 10:06 am UTC

1) not necessary.



why would you do ddl in a hot active database?

Laly, August 18, 2004 - 5:53 am UTC

why are locks on underlying tables not necessary when we open a cursor ? I can't figure it out

For me, when i open a cursor, the results are frozen at this very moment and nothing that can happen in other session should prevent me to get this results when i want to fetch it.

No, in our production databases, we are not doing DDL, only truncating table on working tables by SQL*Loader.


Laly.

Tom Kyte
August 18, 2004 - 8:34 am UTC

the results will either be retrieved consistently (as of the point in time your query began) OR you will recieve an error at query fetch time.

consider the infamous "ora-1555"?

truncate *IS DDL*

if you don't want to do DDL, use "replace" in sqlldr. it'll delete and then insert.

then, instead of 8031, you might be seeing 1555 if you do not size your rbs's appropriately.



Laly, August 18, 2004 - 8:43 am UTC

Thanks Tom, i understood.


A reader, September 09, 2004 - 2:33 pm UTC

I am running "select count(*) from table"
and I am getting ORA-08103 error.
I am pretty sure that no other sessions are trying to do any sort of DDL on that table.

Do you know why?

Tom Kyte
September 09, 2004 - 3:00 pm UTC

only things I'm aware of is a truncate, move or drop of the segment you were reading (which could just as easily be an INDEX segment bear in mind, not the table) -- anything that changes the data_object_id.

you might want to analyze the table, validate structure (or index if it is reading the index) just to make sure there isn't something structurally wrong.

A reader, September 09, 2004 - 5:21 pm UTC

I did analyze table validate structre and estimate statistics on this particular table. There are no indexes.

I found something in the alert.log file like this....

*** SESSION ID:(34.31) 2004-09-09 17:00:15.134
***
Corrupt block relative dba: 0x01c175cd (file 7, block 95693)
Bad check value found during user buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x01c175cd
last change scn: 0x0001.eae1c447 seq: 0x1 flg: 0x06
consistency value in tail: 0xc4470601
check value in block header: 0x870c, computed block checksum: 0x300
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x01c175cd (file 7, block 95693) found valid data


Does this makes sense for the problem?

Tom Kyte
September 09, 2004 - 6:29 pm UTC

yes, you have an issue there -- either the disk is going bad, or the interface to the disk is going bad or there is a bad interaction between Oracle and the file system..


This can cause an 8103 -- Oracle read the block and looked at the object ids on it and said "hey, this doesn't belong to the thing I'm reading -- object no longer exists"

What happened was it sounds like the bits got flipped on portions of the block.

Here, a reread got good data.

Definitely time to get your DBA and SA together and determine "course of action" -- first and foremost being "make sure our backups are okey dokey", then look to testing hardware wise this equiqment -- look in your system logs (OS logs), you might find hard/soft IO errors being reported as well.

A reader, February 10, 2006 - 2:24 am UTC


A reader, September 24, 2007 - 5:16 pm UTC

Tom,

We have a situation similar to the one you mentioned wherein
we are getting "ORA-08103: object no longer exists" as we are trying to read the data and there is a job which truncates the table. Can we run some sql to find out the table is being accessed so we don't execute the truncate and wait till it finishes.

Thanks.
Tom Kyte
September 26, 2007 - 8:53 pm UTC

well, wouldn't you just end up in a situation whereby you NEVER truncate the table? (because someone is always accessing it)

seems strange to truncate a table that other (uncoordinated) sessions are reading - what is the business case that makes this a reality???

A reader, September 27, 2007 - 2:07 am UTC

Tom,

The table stores login information regarding web sessions which is needed for a day or so and later can be truncated. There is no partition feature installed due to licensing costs.

Thanks.


Tom Kyte
September 27, 2007 - 6:56 am UTC

then the person running the query that gets the 8103 should just treat it as if it where "no data found" and be done with it.

else do something like:

create table t1 ( .... );
create table t2 ( .... );
create or replace synonym T for t1;


now, you use T in your application, you want to truncate T1, empty it out, so you now:

create or replace synonym S for T2; <<=== new stuff uses T2, not T1
dbms_job.submit( 'execute immediate ''truncate table t1'';', sysdate+1/24 );
commit;

that'll point to t2 and in an hour, truncate T1

A reader, November 15, 2008 - 3:09 pm UTC

Tom,

We have a table which is partitioned and has 365 partitions, and some of the columns are having local indexes to support PARTITION RANGE ALL since we are not using the Date_Created Clause in the queries to take advantage of partition pruning.

Queries such as :

Select max(id) from t where asin := ? where we have local index on ASIN.

During this run the Rolling window partition kicks in and drops the oldest partition resulting the infamous ORA-08103: object no longer exists.

I was thinking of resetting the recyclebin parameter to be ON instead of OFF, since the dropped partition will n't release the space already allocated to it and the query can check the new segment name and retrieve the data from there.

Is that a viable solution, If not can you please let me know some solution where in we can avoid this error. The application is 24 * 7 and cannot expect downtime for executing the RWProcess.

Thanks


Tom Kyte
November 18, 2008 - 6:53 pm UTC

...
We have a table which is partitioned and has 365 partitions, and some of the
columns are having local indexes to support PARTITION RANGE ALL since we are
not using the Date_Created Clause in the queries to take advantage of partition
pruning.


....

that doesn't convey useful information.


o columns are having local indexes to support PARTITION RANGE ALL
so? what? not sure what you are trying to say.

o not using the Date_Created Clause in the queries to take advantage of partition
pruning.
give we have no clue how you partitioned, so?


... I was thinking of resetting the recyclebin parameter to be ON instead of OFF,

....
and that would do NOTHING for your stated problem, you are scanning an object that *no longer exists*. dropping a partition drops it (no recyclebin, there is no "undrop partition", it is free to be reused and it is (in fact the recycle bin HELPS PREVENT the 8103 in general, it certainly does not CAUSE IT)



Let me ask you this - how is it you have an application that might need data you just decide to drop in the middle of it's work? Something seems horribly amiss here.

Here are the details

A reader, November 19, 2008 - 3:13 pm UTC

Not sure if I have completely stated the problem, so sorry about that.

The table is partitioned using Range Based partitioning on Date_Created and has 365 partitions.

Now the application tier has queries give me MAX(Id) based on a particular ASIN where ASIN has a local index on it, thus has 365 index partitions.

Now during execution all PARTITIONS will be scanned using this index to get the MAX(Id). In the meantime we have a process which kicks in to drop the oldest partition and creates a new partition on a daily basis.

Lets suppose during this interval the oldest partition gets dropped while the query is still executing,What will happen during the Merge of resultset to provide the MAX(Id).

I think it will fail stating "object no longet exists". This is what I was trying to project so as to get a clean solution to avoid "object no longer exists" error.
Tom Kyte
November 24, 2008 - 11:00 am UTC

object no longer exists would only happen if the space was released AND reused. If the space was just released - the blocks we are scanning through won't change, it'll run to completion.

seems that the index on ID should be global if you ask me.... then do partition operations with index maintenance turned on - sure the (relatively INFREQUENT) partition operation will take longer, but so what - it is "online" and everything continues to run and the benefit of the global indexes will be felt constantly by probably most every query you execute.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library