Skip to content

Trivia question: Why does the 2nd Drop Work?

May 21, 2013

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.

About these ads

From → Oracle, SQL

21 Comments
  1. nice one, it already costed me 2 hours this morning.
    Is the error ORA-14452 ?

    The only “solution” ( but I bet, this doesn’t count ) I found is:

    sokrates@hekla[602]> create table foo( i int primary key );
    
    Table created.
    
    sokrates@hekla[602]> create table fooo( i references foo(i) );
    
    Table created.
    
    sokrates@hekla[602]> create trigger tr_se after servererror on schema begin execute immediate 'drop table fooo'; end;
      2  /
    
    Trigger created.
    
    sokrates@hekla[602]> drop table foo;
    drop table foo
               *
    ERROR at line 1:
    ORA-02449: unique/primary keys in table referenced by foreign keys
    
    
    sokrates@hekla[602]> /
    
    Table dropped.
    
    
    • Nice try :) I have to give you points, because does fit the bill, technically – but my answer is a bit simpler.

      And no, it’s not ORA-14452 – it’s just an ordinary table, not a global temporary.

      Hint #1: It is a plausible situation that one might conceivably see in the wild.

      Hint #2: There are no triggers involved.

  2. Toon Koppelaars permalink

    I was thinking dropping a partitioned table with self-referencing FK, where rows reference rows in other partitions. The dropping of a sub-partition might cause issues for the FK… But alas, no the following just works.

    CREATE TABLE foo
    (c1 NUMBER not null
    ,c2 NUMBER
    ,primary key(c1)
    ,foreign key (c2) references foo(c1))
    PARTITION BY SYSTEM (
       PARTITION p1,
       PARTITION p2
    )
    /
    
    insert into foo partition(p1) values(1,1);
    insert into foo partition(p2) values(2,2);
    
    insert into foo partition(p1) values(3,2);
    insert into foo partition(p2) values(4,1);
    
    commit;
    
    drop table foo;
    
    • Nice try, I think – it’d be a bit of a bug I think though if constraints were being validated in between the dropping of partitions of a table when we want to drop the whole table :)

      Hint #3: the behaviour, I believe, is by design and not an Oracle bug.

  3. Toon Koppelaars permalink

    Is the error you got an ORA-04061?

    • No, sorry :) there’s no database packages, procedures or functions in this schema.

      Hint #4: I was naughty and didn’t mention that on the first go, the drop actually raises two exceptions.

  4. What about deferred primary keys? :)

    00:22:24 HR@sandbox> create table foo (n number primary key initially deferred);

    Table created.

    Elapsed: 00:00:00.06
    00:22:27 HR@sandbox> insert into foo values (1);

    1 row created.

    Elapsed: 00:00:00.01
    00:22:29 HR@sandbox> insert into foo values (1);

    1 row created.

    Elapsed: 00:00:00.00
    00:22:29 HR@sandbox> drop table foo;
    drop table foo
    *
    ERROR at line 1:
    ORA-02091: transaction rolled back
    ORA-00001: unique constraint (HR.SYS_C008380) violated

    Elapsed: 00:00:00.03
    00:22:34 HR@sandbox> drop table foo;

    Table dropped.

    Elapsed: 00:00:00.10

    • First prize! Well done :)

  5. Could it be as simple as someone was running a DML during the first drop and that lock was gone during the second?

    • Sorry Arup, I didn’t notice your comment was waiting in my queue. Nope, there was no-one else running DML during the first drop :)

  6. Jacco H. Landlust permalink

    Have you been fiddling aroud with disable table lock?

    • Nope :)

  7. sokrates@11.2 > create table foo ( i int primary key, j references foo(i) deferrable );
    
    Table created.
    
    sokrates@11.2 > set constraints all deferred;
    
    Constraint set.
    
    sokrates@11.2 > insert into foo values(1,2);
    
    1 row created.
    
    sokrates@11.2 > drop table foo;
    drop table foo
    *
    ERROR at line 1:
    ORA-02091: transaction rolled back
    ORA-02291: integrity constraint (SOKRATES.SYS_C00117392) violated - parent key not found
    
    
    sokrates@11.2 > drop table foo;
    
    Table dropped.
    
    
    • Yes, that’s it – well done :)

      (Note for anyone reading this later – Kirill’s answer was first, but it had to wait in my spam queue until I woke up this morning, so Matthias and others wouldn’t have seen it)

  8. Toon Koppelaars permalink

    From my fellow Oakie Charles Hooper:

    CREATE TABLE FOO (
    C1 NUMBER,
    C2 VARCHAR2(10),
    CONSTRAINT FOO_CHECK UNIQUE (C1) INITIALLY DEFERRED DEFERRABLE);

    INSERT INTO FOO VALUES (1,’1′);
    INSERT INTO FOO VALUES (1,’1′);

    DROP TABLE FOO PURGE;

    DROP TABLE FOO PURGE;

    SQL> CREATE TABLE FOO (
    2 C1 NUMBER,
    3 C2 VARCHAR2(10),
    4 CONSTRAINT FOO_CHECK UNIQUE (C1) INITIALLY DEFERRED DEFERRABLE);

    Table created.

    SQL>
    SQL> INSERT INTO FOO VALUES (1,’1′);

    1 row created.

    SQL> INSERT INTO FOO VALUES (1,’1′);

    1 row created.

    SQL>
    SQL> DROP TABLE FOO PURGE;
    DROP TABLE FOO PURGE
    *
    ERROR at line 1:
    ORA-02091: transaction rolled back
    ORA-00001: unique constraint (TESTUSER.FOO_CHECK) violated

    SQL> DROP TABLE FOO PURGE;

    Table dropped.

    • Charles got it :)

  9. Toon Koppelaars permalink

    Here is another example from an Oaktable member:

    CREATE TABLE FOO (
    C1 NUMBER,
    C2 VARCHAR2(10));

    INSERT INTO FOO VALUES (1,’1′);

    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
    INSERT INTO FOO VALUES (2,’2′);
    EXECUTE IMMEDIATE ‘DROP TABLE FOO PURGE';
    END;
    /

    DROP TABLE FOO PURGE;

    ===================================

    SQL> CREATE TABLE FOO (
    2 C1 NUMBER,
    3 C2 VARCHAR2(10));

    Table created.

    SQL>
    SQL> INSERT INTO FOO VALUES (1,’1′);

    1 row created.

    SQL>
    SQL> DECLARE
    2 PRAGMA AUTONOMOUS_TRANSACTION;
    3 BEGIN
    4 INSERT INTO FOO VALUES (2,’2′);
    5 EXECUTE IMMEDIATE ‘DROP TABLE FOO PURGE';
    6 END;
    7 /
    DECLARE
    *
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
    ORA-06512: at line 5

    SQL>
    SQL> DROP TABLE FOO PURGE;

    Table dropped.

    • Only SQL was involved, but still this is an interesting contribution, thanks.

  10. Mette permalink

    Hmmm … It mmust have to do with the implicit commits before and after DDL … now I just need to figure out the statements :-)

  11. Mette permalink

    Here we go:

    create table foo (
    id number primary key,
    parent_id number
    );

    alter table foo add constraint parent_fk foreign key (parent_id) references foo initially deferred;
    insert into foo values (1,1);
    insert into foo values (2,3);

    drop table foo;
    drop table foo;

    • Well done Mette :)

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 207 other followers

%d bloggers like this: