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.
Oracle Scratchpad 
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!
Bye,
Antonio
Comment by lascoltodelvenerdi — November 25, 2009 @ 3:44 pm GMT Nov 25,2009 |
It would be great to have transactional DDL!
… like DB2 :-)
Comment by Connor McDonald — November 26, 2009 @ 2:38 am GMT Nov 26,2009 |
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 |
> … like DB2
… or like PostgreSQL :)
Comment by Hans — November 26, 2009 @ 7:53 am GMT Nov 26,2009 |
On an 11G R1.1.0.6.0 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 |
From the first extent without any exceptions:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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; DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ------------------------------------ 3619507 3619508 3619509 3619510 3619511 3619512 6 rows selected. SQL> select block_id from dba_extents where segment_name = 'T55' and owner = user; BLOCK_ID ---------- 3619504 3619512 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 / 3619507 3619508 3619509 3619510 3619511 PL/SQL procedure successfully completed. SQL>Comment by Valentin Nikotin — March 7, 2012 @ 7:35 pm GMT Mar 7,2012 |