In Oracle, the dual table is a special kind of one-row and one-column database table. dual is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record. The value of that record is X. Note that the outer query references the dummy table called dual the dual table is used in Oracle when you need to run SQL that does not logically have a table name.

The dual table was created by sql.bsq when database was created , so every database will have dual table, the owner of dual table is SYS:

 
SQL> SELECT owner, TABLE_NAME FROM dba_tables WHERE TABLE_NAME='DUAL';
 
OWNER                                                        TABLE_NAME
------------------------------------------------------------ ------------------------------------------------------------
SYS                                                          DUAL
 

The base issue you should always remember and keep is: DUAL table should always have 1 ROW. Dual is a normal table with one dummy column of varchar2(1).

This is basically used from several applications as a pseudo table for getting results from a select statement that use functions like sysdate or other prebuilt or application functions.

If DUAL has no rows at all some applications (that use DUAL) may fail with NO_DATA_FOUND exception. If DUAL has more than 1 row then applications (that use DUAL) may fail with TOO_MANY_ROWS exception.

So DUAL should ALWAYS have 1 and only 1 row.

The DUAL table is used because the relational model does not have a placeholder for calculations because every command must be a SQL statemenet. With dual table you can do a lot of thing about Oracle, for example

Call system function

 
  SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM dual;--get current system time
 
  SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;--get host name
 
  SELECT SYS_CONTEXT('USERENV','language') FROM dual;--get current locale
 
  SELECT dbms_random.random FROM dual;--get a random number
 

Get value of Sequence

 
  SELECT your_sequence.NEXTVAL FROM dual;--get next value
 
  SELECT your_sequence.currval FROM dual;--get current value
 
 

Do calculation

 
 
    SELECT 7*9 FROM dual;
 
  ------
        63
 

Get System Change Number

 
SQL> SELECT dbms_flashback.get_system_change_number FROM dual;
 
GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1047488