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.

21 thoughts on “Trivia question: Why does the 2nd Drop Work?

  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.
    
    

    Like

    1. 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.

      Like

  2. 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;
    

    Like

    1. 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.

      Like

    1. 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.

      Like

  3. 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

    Like

  4. 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.
    
    

    Like

    1. 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)

      Like

  5. 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.

    Like

  6. 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.

    Like

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

    Like

  8. 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;

    Like

Comments are closed.