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.

APEX Dynamic Action silently fails – a story
Deploying Application Express on the Command Line

Comments

  1. Matthias Rogel
    21 May 2013 - 5:50 pm

    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.
    
    
    • Jeffrey Kemp
      21 May 2013 - 6:19 pm

      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
    21 May 2013 - 8:50 pm

    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;
    
    • Jeffrey Kemp
      21 May 2013 - 9:02 pm

      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
    21 May 2013 - 9:31 pm

    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. Kirill Leontiev (@be_here_now_97)
    22 May 2013 - 12:24 am

    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

  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
    22 May 2013 - 2:08 am

    Have you been fiddling aroud with disable table lock?

  7. Matthias Rogel
    22 May 2013 - 3:38 am

    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.
    
    
    • Jeffrey Kemp
      22 May 2013 - 8:30 am

      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
    22 May 2013 - 3:42 am

    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.

  9. Toon Koppelaars
    22 May 2013 - 3:45 am

    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.

    • Jeffrey Kemp
      22 May 2013 - 8:33 am

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

  10. Hmmm … It mmust have to do with the implicit commits before and after DDL … now I just need to figure out the statements 🙂

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

Leave a Reply

Your email address will not be published / Required fields are marked *