DUAL Table in Oracle

DUAL is a one-row, one-column table that exists in every Oracle database. Its job is to give SELECT something to select from when the expression you want does not come from a table:

SELECT SYSDATE FROM dual;
SELECT 2 * 21 AS answer FROM dual;

From Oracle Database 23ai onward the FROM dual part is optional, so SELECT SYSDATE; works too. Oracle's SQL reference states it this way: "Beginning with Oracle Database Release 23, it is now optional to select expressions using the FROM DUAL clause." On 19c and earlier, leaving out FROM raises ORA-00923: FROM keyword not found where expected.

What is in the table

DESC dual
SELECT * FROM dual;

 Name     Null?    Type
 -------- -------- -----------
 DUMMY             VARCHAR2(1)

D
-
X

DUAL is owned by SYS and is reachable by every user under the plain name DUAL through a public synonym. It has one column, DUMMY, and one row containing X. The value is irrelevant; what matters is that there is exactly one row, so any expression you select is returned exactly once.

Everyday uses

CREATE SEQUENCE order_seq;

-- current date, time and session details
SELECT SYSDATE, SYSTIMESTAMP, USER FROM dual;
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') AS container FROM dual;

-- try out a function or a format mask
SELECT TO_CHAR(DATE '2026-10-08', 'Day, DD Mon YYYY') FROM dual;
SELECT ADD_MONTHS(DATE '2026-01-31', 1) FROM dual;

-- strip characters from a string
SELECT TRANSLATE('ab#c@1$', 'x#@$', 'x') AS cleaned FROM dual;

-- get the next value of a sequence
SELECT order_seq.NEXTVAL FROM dual;

The TRANSLATE example returns abc1: every character in the second argument that has no counterpart in the third is removed. The leading x is there because the third argument cannot be an empty string, which Oracle treats as NULL.

Generating rows

DUAL has one row, but a hierarchical query can multiply it. This is the standard Oracle row generator:

SELECT LEVEL AS n
FROM   dual
CONNECT BY LEVEL <= 5;

         N
----------
         1
         2
         3
         4
         5

The same trick builds a calendar:

SELECT DATE '2026-10-01' + LEVEL - 1 AS day
FROM   dual
CONNECT BY LEVEL <= 31;

LEVEL is the right pseudocolumn here. Very large CONNECT BY generators use a lot of memory; for millions of rows, cross join two smaller generators.

Literal rows

UNION ALL over DUAL is the traditional way to build a small inline data set, often as the source of a MERGE:

SELECT 1 AS id, 'Open'   AS status FROM dual UNION ALL
SELECT 2,       'Closed'           FROM dual;

In 23ai and later, a table value constructor does this without DUAL:

SELECT * FROM (VALUES (1, 'Open'), (2, 'Closed')) t (id, status);

With and without DUAL in 23ai and later

Statement	19c and earlier	23ai and later
SELECT SYSDATE FROM dual;	Works	Works
SELECT SYSDATE;	ORA-00923	Works
SELECT 1 + 1;	ORA-00923	Works
SELECT LEVEL FROM dual CONNECT BY LEVEL <= 5;	Works	Works

Existing code does not need to change. Keep FROM dual in scripts that must run on older releases, and note that row-generator queries still need a FROM clause to attach CONNECT BY to.

Performance: FAST DUAL

Selecting from DUAL does not read the table. Since Oracle 10g the optimizer recognises it and skips the I/O whenever the query does not reference the DUMMY column. You can see this in the execution plan:

EXPLAIN PLAN FOR SELECT SYSDATE FROM dual;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

If you select DUMMY itself, or SELECT *, the plan shows a normal table access and Oracle performs logical I/O. That cost is tiny, but it is one more reason to select only the expression you need.

You do not need DUAL in PL/SQL

Old code often assigns values with a query:

SELECT SYSDATE INTO v_today FROM dual;
SELECT order_seq.NEXTVAL INTO v_id FROM dual;

Each of these is a SQL statement executed from PL/SQL, with a context switch between the two engines. Plain assignment does the same thing:

DECLARE
  v_today DATE;
  v_id    NUMBER;
BEGIN
  v_today := SYSDATE;
  v_id    := order_seq.NEXTVAL;   -- allowed in PL/SQL expressions since 11g
END;
/

Common mistakes

• Changing DUAL. Privileged users are able to insert into, delete from or even drop SYS.DUAL. Never do it. Internal code and countless applications assume exactly one row; an extra row makes SELECT ... INTO ... FROM dual fail with ORA-01422: exact fetch returns more than requested number of rows, and an empty DUAL makes it fail with ORA-01403: no data found.
• Creating your own DUAL. A table named DUAL in your schema takes precedence over the public synonym for your sessions and loses the FAST DUAL optimisation. If queries against DUAL behave oddly, check with SELECT owner, object_type FROM all_objects WHERE object_name = 'DUAL';. You should see a SYS table and a PUBLIC synonym, nothing else.
• Using DUAL to test whether a row exists. SELECT 1 FROM dual WHERE EXISTS (SELECT 1 FROM orders WHERE ...) is valid, but it returns no row when nothing matches, so a SELECT ... INTO around it raises NO_DATA_FOUND. Use SELECT COUNT(*) INTO ... FROM orders WHERE ... AND ROWNUM = 1 instead, which always returns one row.
• Expecting more than one row from a plain select. SELECT expr FROM dual always returns a single row. To get several, use CONNECT BY, UNION ALL or a VALUES constructor as shown above.

DUAL in other databases

Code that selects from DUAL is not portable everywhere:

Database	Equivalent
MySQL, MariaDB	FROM DUAL is accepted as a no-op; SELECT NOW(); also works
PostgreSQL, SQL Server	No DUAL; omit the FROM clause
IBM Db2	SYSIBM.SYSDUMMY1, or a VALUES statement

If you are writing SQL for 23ai or later only, omitting FROM dual gives you statements that also run unchanged on PostgreSQL, MySQL and SQL Server.

Further reading

• SQL Language Reference: Selecting from the DUAL Table
• SQL Language Reference: SELECT
• SQL Language Reference: Hierarchical Queries