The insert does work, however the kernel "knows" about DUAL and so only returns the first row when you select from it. However, if you select count(*) you'll get the real answer.

SQL> insert into dual select * from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dual;

D
-
X

SQL> select count(*) from dual;

  COUNT(*)
----------
	 2

How's that for smart?

In previous versions of Oracle I have seen this happen. However, I've tried to insert into dual in more recent versions and was unable to. No error messages, but no data added to dual either.

I can't tell you how this happened, but I can suggest an unsupported way of making sure that it never happens again. The APT script replace_dual.sql can replace the SYS.DUAL table with a view onto X$DUAL. Once that's done, it will not be possible for extra rows to appear in DUAL again. From a performance point of view, there is a slight increase in the parse overhead, but a slight reduction is the execution cost of statements that refer to DUAL. Once again, this is unsupported - mentioned for information only.

In one of the databases here, count(*) from dual gave me 3. I deleted two rows based on the rowid's and now things are working fine. I was wondering how it could happen in the first place. No, no one logging in as SYS inserted this. Then how?