Fun with DUAL Table

Posted by Dirk Nachbar on Wednesday, December 02, 2009
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
Categories: