Fun with DUAL Table

Try this "little hack":

connect to Database as sysdba and execute following commands:

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:

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

select * from dual;
D
-
X

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

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

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