Oracle Scratchpad

November 25, 2009

Drop Table

Filed under: Infrastructure — Jonathan Lewis @ 1:34 pm GMT Nov 25,2009

Here’s an interesting question raised by Uwe Hesse: “if you drop a table when someone else is querying it, what happens to their query?”

It’s an interesting demonstration of how many features you have to consider before you reach a conclusion – and while you may start by assuming that the question is irrelevant to a production system, when you think a little more carefully that’s not necessarily the case.


  1. Often I’ve tried to imagine how a 24/24h system can do such things.

    Well, you can stop the service for a moment…but what if that moment means millions of euros?

    It would be great to have transactional DDL!


    Comment by lascoltodelvenerdi — November 25, 2009 @ 3:44 pm GMT Nov 25,2009 | Reply

  2. It would be great to have transactional DDL!

    … like DB2 :-)

    Comment by Connor McDonald — November 26, 2009 @ 2:38 am GMT Nov 26,2009 | Reply

  3. I remember transaction DDL in Ingres around 15-20 years ago.
    It came as quite a shock when I found Oracle committed after creating a ‘temporary’ table. Once I learnt I didn’t need all the temporary tables (because the concurrency mechanism was better), it wasn’t an issue.

    Comment by Gary — November 26, 2009 @ 4:46 am GMT Nov 26,2009 | Reply

  4. > … like DB2

    … or like PostgreSQL :)

    Comment by Hans — November 26, 2009 @ 7:53 am GMT Nov 26,2009 | Reply

  5. On an 11G R1. test system I get the following error on the querying session:-

    ORA-08103: object no longer exists.

    Comment by Tony Sleight — November 26, 2009 @ 1:37 pm GMT Nov 26,2009 | Reply

  6. From the first extent without any exceptions:

    Connected to:
    Oracle Database 11g Enterprise Edition Release - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    SQL> set serveroutput on
    SQL> create table t55 as select lpad('X',4000,'X') X from dual connect by level <= 6;
    Table created.
    SQL> select dbms_rowid.rowid_block_number(rowid) from t55;
    6 rows selected.
    SQL> select block_id from dba_extents where segment_name = 'T55' and owner = user;
    SQL> declare
      2    s sys_refcursor;
      3    bno number;
      4  begin
      5    open s for select dbms_rowid.rowid_block_number(rowid) from t55;
      6    execute immediate 'drop table t55 purge';
      7    loop
      8      fetch s into bno;
      9      exit when s%notfound;
     10      dbms_output.put_line(bno);
     11    end loop;
     12    close s;
     13  end;
     14  /
    PL/SQL procedure successfully completed.

    Comment by Valentin Nikotin — March 7, 2012 @ 7:35 pm GMT Mar 7,2012 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by