In my last course, I had one student asking the question: „What happens if we do select on a table and someone else drops it during it?“ Of course, you cannot drop a table while an open transaction uses the table. But what for a select: will it go through? Although this question seems to be a little academic – which I responded immediately – there was no obvious answer, I was aware of. So I did setup a little test case for it.
I like to answer questions with practical examples if possible. The drop table has no effect at all on the select statement for small tables, of course, if done after the select started, because the select is just too fast to notice the drop. So my test table is more than 4 Gig in size and has got 117611904 rows in it. Surprisingly, there is a difference in the outcome, depending whether you drop the table with purge or not.
SQL> select count(*) from sales;
COUNT(*)
----------
117611904
Elapsed: 00:00:55.74
I have two sessions connected. In the first session, I do select. In the second session, I drop the table. Without dropping, the select needs about 1 minute to complete as shown above. Now the drop table without purge (basically just renaming the table in the data dictionary, enabling flashback to before drop since 10g):
SQL> select count(*) from sales;
COUNT(*)
----------
117611904
Elapsed: 00:01:01.25
SQL> drop table sales;
Table dropped.
The drop table statement completed much faster than the select above. Apparently, the select keeps running, fetching the rows from the „dropped“ table in the recyclebin.
Now I really drop the table:
SQL> flashback table sales to before drop;
Flashback complete.
SQL> select count(*) from sales;
select count(*) from sales
*
ERROR at line 1:
ORA-08103: object no longer exists
Elapsed: 00:00:05.17
SQL> drop table sales purge;
Table dropped.
This interrupts the select statement in the first session. Well, I doubt that there is much practical implication of this, but I found it interesting in spite of. Thanks to Mr. Heinz Schmitz for raising that question 🙂
Pickleball spielen 
#1 von coskan am Oktober 27, 2009 - 16:17
Addition 🙂
Same thing happens if you truncate the table as well during the querying
#2 von Uwe Hesse am Oktober 30, 2009 - 08:12
Coskan, you are right, thank you!
But did you know about the difference, depending whether you drop with purge or not? That is actually the good thing about answering questions with examples (or call it test cases), in my view. If you answer a question only verbal, there are (at least) 3 possible cases:
1) Your theory/answer is correct
2) Your theory is good and plausible, but it ain’t so in reality
3) Your answer was true in the past – but things have changed meanwhile.
Many an expert would have answered the question with: „The select returns an error message“ – which was true until 10g (case 3). You may have noticed that there is from time to time an argument in the Oracle Community, whether test cases are useful or not. I think, case 2 and 3 (apart from other valid points – you know, I am an advocat for test cases) are very strong indications in favor of their usefulness. You may be an experienced DBA, Consultant, Instructor – so you (should!) know how things used to be in the last couple of releases 🙂
#3 von Jonathan Lewis am November 22, 2009 - 12:11
Uwe,
I think the behaviour is a result of the segment header block being cleared by the purge or truncate.
I see from your example that it took a few seconds for the 8103 error to appear – it’s possible that this occurs when your session tries to re-read the segment header block to find the next extent it has to scan in the table.
This means the effects could appear to be a little random – for example, what happens if you drop a table which has just one extremely large extent; or what happens if you drop a table when the query is accessing it by index and doesn’t need to re-read the extent map ?
#4 von Uwe Hesse am November 22, 2009 - 15:39
Jonathan,
thank you for engaging yourself in this probably rather academic question. So in your opinion, we cannot generalize the answer to „Before 10g, or if you drop with PURGE, the select will return an error message“? That is quite well possible. I think, we could at least generalize the answer to: „The select will return the correct number of rows or an error message“.
Maybe, someone likes to setup some test cases: One with a very large single extent and one with an index on a NOT NULL column, to find out 🙂
I really like to get aware again of the extreme complextity of Oracle Databases, that gets revealed by trying to answer even the apparently simplest questions – if you just dig deep enough…
#5 von Jonathan Lewis am November 24, 2009 - 20:24
Uwe,
Reading your comment about „academic“, it crossed my mind that when you do partition maintenance (such as exchange partition) and then drop the exchanged partition, Oracle has been able to complete current queries quite successfully. It’s possible that there is an element of luck in this that may have changed in recent upgrades. The question may not be quite so academic as we first thought.
I have tried the indexed method, by the way. It’s not conclusive, of course, but I’ve run queries that surived about 5 seconds before failing with ORA-08103 if using a tablescan, but ran to completion after more than 20 seconds when doing a full data scan through an index.
I also have a vague memory that Oracle re-reads the extent map block(s) after every 10 extents, so a query against a table (or partition) with fewer than ten extents (if I’m correct) may always complete successfully. This may mean that people are more likely to see ORA-08103 in more recent versions of Oracle because they’ve decided to move their data into tablespaces using automatic extent sizes (starting with 16 extents of 64KB each – by default).
YOu have to love the way there are always more layers to the onion. (What’s that song from Lion King – „there’s more to know than can ever be known“)
#6 von Uwe Hesse am November 24, 2009 - 21:18
Jonathan,
thank you again for your valuable input! I really wonder how you get aware of these kind of informations, for example about the re-reading of the extent map 🙂
True, with autoallocate, you start with 64k extents until you reach a segment size of 1M (16 extents), after that you would get 1M extents etc.
On the other hand, these tables are so small in size that the likelihood to get the error decreases because the select completes so fast. That tempts me to setup a test with a tablespace with a large uniform size and a table with 10 resp. 11 extents, in order to verify the „after 10 extents re-read of extent map“ theory.
But not tonight (9 PM in my timezone currently). By the way, I had to consult my dictionary to see what „cull“ means 🙂
#7 von Jonathan Lewis am November 25, 2009 - 12:39
Uwe,
I really wonder how you get aware of these kind of informations, for example about the re-reading of the extent map.
It’s just stuff I accumulate over the years from seeing so many different production systems. I’ve got a note at http://www.jlcomp.demon.co.uk/one_extent.html about this one. I think I wrote it because of an oddity I happened to notice at a site where they had objects with several thousand extents of 32KB.
#8 von Uwe Hesse am November 25, 2009 - 18:05
Jonathan,
I have just tested the „10-Extents-Theory“. With 11gR2 on Oracle Enterprise Linux, I created a uniform size 100m tablespace and stored 2 tables in it: One with 10 extents, one with 11 extents (all filled with rows). In BOTH cases, the select runs to completion in spite of dropping the table (with purge!) in another session.
In other words: Even with tables of Gigabyte sizes, the select does not interrupt with an error message, if the table has only few extents – probably also in versions before 10g.
In my original test in the posting, I used a tablespace with autoallocate, resulting in much more than 11 extents for the table.
#9 von Devi am März 18, 2012 - 21:18
For database version : 11.2.0
User has faced ORA-08103 „object no longer exists“ error. User has confirmed that the object is existing and it is MVIEW.
I dont know how to proceed further to find the cause and dont know what steps i should take to find the possibilities of investigation.
I am in the situation to take quick action for this error. I request you to help me with this.
#10 von Uwe Hesse am März 19, 2012 - 11:23
Devi, you may have noticed that we took a DROP TABLE scenario here for research – in our case, the table definitely did NOT exist any more after the SELECT was started.
You case is apparently different. I have neither the time nor the ambition to troubleshoot your problems, sorry.
Please contact Oracle Support for these kind of requests. Alternatively, you may try a forum from the LINKS list on the right like http://forums.oracle.com/forums/forum.jspa?forumID=61&start=0