What Is DUAL Table In Oracle?

Most of us might have at least once queried the DUAL table of our Oracle database.

But, what is this DUAL table? Who owns it? What privilege is required to select from dual table?  Well . This article explains the concept of dual table.

Dual is a one row table created along with the data dictionary in Oracle. Table has a single column called “ Dummy” of datatype varchar2 with a value “ X ” .

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

SQL> select * from dual;
DUM
—
X

SYS owns this table. It is used to select any Pseudocolumn. In simple words, to select values like sysdate , addition of two numbers or the current user , which is not actually present in any table.

SQL> select sysdate from dual;
SYSDATE
————————–
Aug 26 2011 13:31:12

SQL> select 2+3 from dual;
2+3
———-
5

SQL> select user from dual;
USER
————-
SYS

SQL> select ‘HELLO’ from dual;
‘HELLO’
—————
HELLO

A user does not require any explicit SELECT privilege to select from the dual table. Imagine a user  having only CREATE SESSION privilege.Even he will be able to query this table. There exists a public synonym for DUAL table. This permits us to select directly from dual instead of SYS.DUAL.

In short, DUAL can be truly referred as a magical table in Oracle.

Article by : Anju