DUAL TABLE ISSUES.

The SYS-owned dual table was introduced by Oracle long back and since then the Application
Development team across the globe has been using this table for some internal processing.

Getting multiple rows in DUAL can cause a variety of known issues.

If multiple rows are encountered,

for 9i and prior releases the DUAL Insertion and Deletion behavior was different from that in 10g and up.

In 9i and earlier versions the DUAL behavior was as follows:

SQL> connect sys/ as sysdba
SQL> select * from dual;
D
-
x
SQL> insert into dual values('Y');
SQL> insert into dual values('Z');
SQL> commit;
SQL> select count(*) from dual;
-> 3 rows
SQL> select * from dual;
-> retrieves only 1 row even if more rows exist
SQL> delete from dual;
SQL> commit;
SQL> select count(*) from dual;
-> 2 rows, only 1 row was deleted

In other words:

A "Delete from dual", statement without a where clause deletes only one row regardless of how many
rows it contains.

An "Insert into dual", works, and you can insert any number of rows, but when executing
"select * from dual", it returns only one row, but "select count(*) from dual", returns the number of rows that really was inserted.

For 10g and up the dual behaviour has changed as follows:
select * from dual;

D
-
X

select count(*) from dual;

COUNT(*)
----------
1

insert into dual select * from dual;

1 rows created.

select count(*) from dual;

COUNT(*)
----------
1

select * from dual;

D
-
X

delete from dual;

1 row deleted.

select * from dual;

D
-
X

select count(*) from dual;

COUNT(*)
----------
1

In Other words,

For 10g the dual behavior has changed to handle the multiple rows issue. But still it accepts multiple insertion to it and there is no way to know how many rows has been inserted as the Select count (*) returns
only 1 regardless of how many rows has been inserted, therefore TRUNCATE the DUAL then inserting the row again is the only way to bypass the issue.

To know exactly how many rows exits in 10g?

Create table test as select * from Dual;

Select count(*) from dual (it will show only 1 row irrespective it has more then 1 row also)

COUNT(*)

----------

1

select count(*) from test; (it will show the exact values)

COUNT(*)
----------------
3

###################################

How to Recreate the Dual Table:

. Shutdown the database

2. Startup the database in upgrade mode:

sqlplus / as sysdba
SQL> startup upgrade


3. Recreate the DUAL table:

create table dual
(dummy varchar2(1))
storage (initial 1)
/
insert into dual values('X')
/
create public synonym dual for dual
/
grant select on dual to public with grant option
/


4. Verify whether the DUAL table layout is correct:

column OWNER format a15;
OBJECT_NAME format a20;
select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED from dba_objects where object_name='DUAL';

Example output:

OWNER OBJECT_NAME OBJECT_TYPE CREATED
--------------- -------------------- ------------------- ---------
SYS DUAL TABLE 16-JUN-09
PUBLIC DUAL SYNONYM 16-JUN-09

5. Restart the database in normal mode

#############################