Home SQL Trivia question: Why does the 2nd Drop Work?

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.

Jeffrey Kemp 21 May 2013

pop-quiz-hotshot / SQL /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Comments

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

Is the error you got an ORA-04061?
- Jeffrey Kemp
  21 May 2013 - 10:00 pm
  
  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.
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
- Jeffrey Kemp
  22 May 2013 - 8:29 am
  
  First prize! Well done 🙂
Arup Nanda
22 May 2013 - 12:32 am

Could it be as simple as someone was running a DML during the first drop and that lock was gone during the second?
- Jeffrey Kemp
  22 May 2013 - 11:04 am
  
  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 🙂
Jacco H. Landlust
22 May 2013 - 2:08 am

Have you been fiddling aroud with disable table lock?
- Jeffrey Kemp
  22 May 2013 - 11:05 am
  
  Nope 🙂

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)

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.
- Jeffrey Kemp
  22 May 2013 - 8:31 am
  
  Charles got it 🙂
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.
Mette
22 May 2013 - 5:01 am

Hmmm … It mmust have to do with the implicit commits before and after DDL … now I just need to figure out the statements 🙂
Mette
22 May 2013 - 5:28 am

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;
- Jeffrey Kemp
  22 May 2013 - 8:37 am
  
  Well done Mette 🙂

Trivia question: Why does the 2nd Drop Work?

About Jeffrey Kemp

Comments

Leave a Reply Cancel reply