Fun with DUAL Table

Try this "little hack":

connect to Database as sysdba and execute following commands:

?

1 2 3 4 5 6 7 8 9 10

select * from dual; D - X select count(*) from dual; COUNT(*) -------- 1 insert into dual values ('Y'); commit;

Connect in another session with a normal user and try to drop one table:

?

1 2 3 4 5 6 7 8 9

conn scott/tiger drop table t1; ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01422: exact fetch returns more than requested number of rows select * from dual; D - X

Cool the dual just shows one row as expected

Go back to the session with the connect as sysdba

?

1 2 3 4

select * from dual; D - X

Upps, here is the same, one row in the dual.

?

1 2 3 4 5 6

create table copy_dual as select * from dual; select * from copy_dual; D -- X Y

And here we can see the two rows :-)
To clean up, just delete the second row in dual table

?

1 2	delete from dual where dummy = 'Y'; commit;

Addendum: Under Oracle 11.2.0.1.0 the error with the drop table command doesn't come up again