yes -- pretty much.

The select .. where rownum=1

just opens the implicit cursor, gets the first index name, drops it and then does it all over again. Eventually, you'll have no indexes left and the select into will raise no data found -- ending the process.

yes, I just glanced at it -- you are right -- that would get the ora-1555 again ;)


begin
loop
select index_name into l_index_name from user_indexes where rownum=1;
execute immediate 'drop index ' || i.index_name;
end loop;
exception
when no_data_found then null;
end;


is what I had -- to flesh it out:

1 declare
2 l_index_name varchar2(30);
3 begin
4 loop
5 select index_name into l_index_name from user_indexes where rownum=1;
6 execute immediate 'drop index ' || l_index_name;
7 end loop;
8 exception
9 when no_data_found then null;
10* end;



compiles and runs (but it'll fail of course when it hits an index used to enforce a constraint!)

I see -- you think that a cursor gets the rows and then gives them to you -- that is the problem here -- the cursor is getting ROWS as you fetch them and eventually -- due to all of your commits -- it cannot get the rows anymore since the rollback doesn't exist. We need to take this construct:

begin
for i in (select index_name from user_indexes) loop
execute immediate 'drop index '||i.index_name||' ';
COMMIT against changes made to the tables behind
the user_indexes view happens here...
end loop;
end;


and remove the looping cursor -- for all of those commits of changes against the data your are querying are causing the 1555. I did that by recoding as an "infinite loop"

declare
l_index_name varchar2(30);
begin
loop
select index_name into l_index_name from user_indexes where rownum=1;
execute immediate 'drop index ' || l_index_name;
end loop;
exception
when no_data_found then null;
end;


that works by looping -- getting the first index it finds -- dropping it (and hence committing -- but that is OK, we don't have a cursor to fetch from anymore) and going back around until eventually all indexes are dropped and we get the no data found exception.

1) is totally wrong. A cursor is just a "state". A pointer to the query plan in the shared pool, the bind variable values, a pointer perhaps to where in the query we last left off....


2) nope.

Have you ever read through a file a record at a time? Same concept applies in general for database tables. When you open a file, you don't "copy it". When you want to read record 2 -- you don't fseek back to the beginning -- you just pick up where you left off.


So, take a simple query like:


select * from t;


and t is a 500 block table with 10 rows / block.


You do this in sqlplus -- so you are fetching 15 rows/call by default(thats the default arraysize).. Ok,

o sqlplus does the first fetch
o the server reads block 1 of the table, gets 10 rows
o the server reads block 2 of the table, gets 5 rows
o 15 rows are returned
o sqlplus does 14 more fetches that don't bother the server (its already in
the clients process)
o sqlplus goes to fetch row 16 - over to the server
o the server gets block 2 again, gets the remaining 5 rows -- it REMEMBERED
where it left off
o the server gets block 3, gets 10 rows
o 15 rows are returned

o now at this point, someone issues "DELETE FROM T"; "COMMIT;". There are NO
rows in T anymore -- but your cursor will still see them all. Why? because
the UNDO for this delete was put in the rollback segments....

o sqlplus displays the 15 rows it just fetched
o sqlplus goes to fetch row 31 -- over to the server again
o the server gets block 3 -- notices "hey, the SCN on this block is too high
for me, it has changes I'm not allowed to see. Let me use the rollback data
to rollback this block to the point in time that it is OK for me to look at
it"
o the server reads from the rollback segment the necessary information, rolls
the block back....
o the server reads the 10 rows off of the block (the 10 deleted rows! they
were undeleleted for us).
o the same thing happens when we go to get the next 5 rows from block 4
o sqlplus gets the data and displays it

(above process repeated over and over)....

o sqlplus goes to fetch row 400 -- over to the server
o server gets the block -- finds the SCN isn't right for it -- goes to rollback
but discovers that the rollback that was generated by that delete is gone --
somebody OVERWROTE it (our rollback segments are too small). The query now
gets an ora-1555.

sure it could -- doesn't even have to be against the same table -- I can show you an ORA-01555 against a table in a read only tablespace -- caused purely by overcommitting...

plz? that seems to be something related to German postal codes, but I don't see the connection to Oracle.


The funny thing about this particular question is -- it is virutally the same example I use in Expert One on One Oracle to explain the very basic concept of read consistency and multi-versioning. You have just stumbled onto Oracles most amazing feature!

You definitely need to read the concepts guide from cover to cover, but start here:

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96524/c21cnsis.htm#2414 <code>


You know now that "porting" from sqlserver to Oracle or vice versa -- is not something to be taken lightly (or to/from informix, db2, whatever). The fundemental principles of locking, concurrency control, how things *work* are totally different in all of these systems. Sure, they all "speak" sql, but they do things very very very differently.

no -- i don't like it, because it sounds like we make a copy of your result set when you open the cursor -- there are not "two" copies.

It is fair to say that when you open a result set, the data that will be retrieved is "pre-ordained". At the moment you open it, we already know what the answer will be -- and we have not touched a single row. It is as if "time stops" for that result set. Regardless of what else happens in the database -- that result set will either

a) be returned as of the point in time it was opened (assuming normal isolation levels in play here)

b) return an error like ora-1555 or ora-8103

So, you can open:

select * from one_billion_row_table

at 9am this morning (and it'll open instantly -- fast, we don't do any work until you actually make us with a fetch)... and then don't fetch anything -- but you go away.


You come back at 12 noon. And you finally fetch rows. While you were gone for 3 hours though, people have been inserting, updating and deleting from this table.

The rows you fetch -- all one billion of them -- will be "as of 9am", you will see NO changes that occured after the point in time your query began -- NONE.


It is as if time stops for that query. It will see no changes - none. All data returned is "as of" the same point in time.

Which is a good thing, consider this query:


select sum(bal) from bank;


Now, assume the table has:

my_checking_account $100
<millions of other rows>
my_savings_account $100


Now, suppose you fire off that query and it reads row 1, 2, 3.... It is in the middle of the rows. At that point in time - I walk up to an Automatic Teller Machine and transfer $50 from checking to savings, now we have:



my_checking_account $50
<millions of other rows>
my_savings_account $150


my select sum() is still running. It finally gets to the end of the table (as if they have a top and a bottom :)

In Oracle -- when we get to that last row, we'll notice "hey, this was not the value when we began, please put it back" . In Oracle we'll read $100 -- not $150.

The select sum(bal) will be returned to you -- and you get a value that actually existed in the database!


without this multi-versioning, the answer you get from the other databases? Well, it is off by at least $50 -- they will have read the $50 twice -- once from row 1 and again on the last row. They will give you a value that never existed at any point in time as the balance.


Can they give you the right answer? Sure, they would lock rows as they read them. So, when the select sum(bal) started - it would lock rows as it hit them. We would not have been able to update row 1 -- we would block and wait and wait and wait and wait. Even worse, we might update the last row (that would work -- because the query hasn't gotten there yet) and then block trying to update the first row. Now the query hits the last row and finds it locked (and cannot read it -- they block on locked data -- Oracle does not). But now the query and the update are deadlocked! with eachtother. The transfer transaction has been blocked and waiting for a long time, the read transaction has been working for a long time -- one of them will totally LOSE right now. One will get an error -- so sorry, you've totally deadlocked with something else, please try again. And the scenario begins all over again ;)

undo segments (also known as rollback segments).

see
</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96524/c21cnsis.htm#17882 <code>

I do not understand your "transactional logic" at all - because the logic seems "really wrong"


do you understand what commit and rollback actually do?
and that databases ARE BORN TO JOIN
and that the logic above is really horrendous? I cannot imagine this is what you really intend.

here is your logic as it stands:

loop over rows randomly from t1
   loop over rows randomly from t2 where t2.col1 = t1.col1
        insert into t3;
        if t2.col1 = '3' 
        then
            ROLLBACK SET RANDOM SET OF ROWS WE JUST INSERTED - might 
            be every single row, might be some arbitrary set of rows
            because sometimes we commit.  But some UNKNOWN RANDOM SET OF ROWS
            will be rolled back.

            insert into error A SINGLE ROW (we just lost a bunch but we 
            log a single row
 
            COMMIT;
        end if;
   end loop
end loop
COMMIT;

basically, that is botched beyond repair.


If your last row you RANDOMLY HIT is a '3' record, you insert NOTHING into t3.

If on the other hand, the last row you RANDOMLY HIT is not a '3' record, you insert and commit into t3 all of the rows since the last time you RANDOMLY HIT a '3' record.


there is nothing good here. You are doing a do it yourself join (loops in loops) that is horrible. Your transactional logic makes no sense at all and leaves the database in sort of a random state after it is done.

How about you phrase your *requirement* in text...

oh, and tie it all back to "execute immediate and rollback segments" :)

... But unfortunately we cannot share the original code. ...

but fortunately, since you are a developer, you can abstract the problem and post something that remotely resembles reality - else no one can comment on anything because we'd be commenting on SOMETHING YOU ARE NOT DOING IN REAL LIFE - which frankly does not make sense and would just be a waste of both of our time.

so, give us a better example that actually DEMONSTRATES YOUR LOGIC flow.

I would guess, you are using select for update, rollback closes all select for update cursors. But, purely a guess - since we have no idea what you are really doing.

I want to hear from them what their actual logic is before commenting on "how"