Trivia question: Why does the 2nd Drop Work?
I have an Oracle database (10g or 11g, doesn’t matter) with an empty schema. I am the only user on the system and I only have one session open.
I create a single table (FOO) and run a few simple SQL commands. Then, I run this, twice in a row without any intermediate steps:
DROP TABLE foo;
The first time, some errors are raised and the table is not dropped.
The second time, it succeeds – the table is dropped without error.
Q. What did I do to cause this behaviour (which is 100% reproducible) – and what errors were reported?
A. Congratulations to Kirill Leontiev, Matthias Rogel, Charles Hooper and Mette who all found the answer!
Spoiler Alert: Don’t read the comments if you don’t want to know the answer straight away.