this article in Russian

 

Table DUAL is very simple. It has only one column and contains only one row with value 'X'.

 

SQL> desc dual
Name                                      Null?    Type                      
----------------------------------------- -------- ----------------------------
DUMMY                                              VARCHAR2(1)                 

SQL> select * from dual;

D
-
X

But nobody is interested in that value 'X'. This table is used to get data with even no relations to DUAL. For example:

SQL> select user from dual;

USER
------------------------------
SCOTT

To get the same result we could run the following query:

select user from emp where rownum <=1

DUAL is used for such kind of queries just because it always exists. This object is in SYS schema and is brought in upon database creation. Nobody (except curious DBA) can make any changes to it. Nevertheless, when you want to play with DUAL (ensure that you are in test database) you could reveal quite interesting things:

SQL> connect / as sysdba;
Connected.
SQL> insert into dual values('X');

1 row created.

SQL> insert into dual values('X');

1 row created.

SQL> select count(*) from dual;

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

SQL> select * from dual;

D
-
X

Despite DUAL now contains three rows, our query retrieves only one. What is that - database has gone crazy since we made changes to the dictionary ? (DUAL, formally, is part of the dictionary)
To say the truth, No !

Anyway, let's go ahead:

SQL> delete from dual;

1 row deleted.

SQL> select * from dual;

D
-
X

SQL> select count(*) from dual;

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

Even we tried to delete all the rows from DUAL, we achieved it only for one row.

Explanation is: optimizer suggests that DUAL, in any case, contains only one row.

Per say, DUAL in some way is not an ordinary table. Here it is:

SQL> select * from dual;
D
-
X
1 row selected.

SQL> ????????????????????;
Statement processed.

SQL> select * from dual;
ADDR     INDX       INST_ID    D
-------- ---------- ---------- -
26683298          0          1 X
1 row selected.

What magic statement was issued to bring such a changes to DUAL ?

The answer is: alter database close.

Here is why: RMAN needs to access DUAL even after database is closed, so DUAL is accessible thereafter, but with different structure and content.

 

Finishing this short article we would like to repeat the following: remember, table DUAL - is part of system dictionary and you never ever should make any changes to it in real database with working users.