Oracle’s Dual Table

On my article about auto increment columns in Oracle Stephane asked about the use of dual in Oracle.

Dual is sort-of a dummy table. It’s a real table, but not one that should ever get updated. It exists in every Oracle database and is useful for troubleshooting and development.

If you describe dual you will see it’s definition:

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

Dual has only one row, but you can select the value of it.

SQL> select * from dual;

D
-
X

Dual can be selected by any user. Since it contains just the one row you can use it to return a single result to you, like

SQL> select sysdate from dual;

SYSDATE
---------
11-AUG-06

Now you could have done this against any single-row table. You could even create a single row table to select results like this, but since Oracle provides the dual table everyone tends to use it.

Thanks for the question Stephane. I think many people use Dual without knowing (or even wondering) what it is.

oracle, database administration, dba, database development

10 thoughts on “Oracle’s Dual Table”

  1. Pingback: hazbjxzkvy
  2. I see we share a common interest! Excellent job on the website!

  3. Duel table is having a column “DUMMY” and its of type VARCHAR2(1).How it can return sysdate,which always have size greater than VARCHAR2(1)? Anybody having an idea about this?…

  4. Twingle,

    Sorry for the delayed response.

    You can actually select all sorts of things from any table. Try this:

    select 4+3, sysdate, 'some text' from user_tables;

    The values will be repeated for every record in user_tables. Basically these values aren’t selected from the table, but are rather provided as constant values, independent of the table contents.

    It’s a bit tricky, but this functionality allows us to format the output like in the following (which also uses the double-pipe concatenate operator):

    select 'The user ' || username || ' has a status of ' || account_status
    from dba_users;

    'THEUSER'||USERNAME||'HASASTATUSOF'||ACCOUNT_STATUS
    --------------------------------------------------------------------------------
    The user MDSYS has a status of EXPIRED & LOCKED
    The user ORDSYS has a status of EXPIRED & LOCKED
    The user EXFSYS has a status of EXPIRED & LOCKED
    The user DMSYS has a status of EXPIRED & LOCKED
    The user DBSNMP has a status of OPEN
    The user SCOTT has a status of OPEN

    Hope this helps.

  5. I find it amusing that the table explicitly created for the purpose of having one row is called dual. Anyone happen to know why that name was chosen? I’m sure there’s some interesting historical etymology. Well, interesting to some of us database + word geeks.

  6. Some Application Servers use the “select ‘X’ from DUAL;” dummy statement to verify the ORacle Connection is working. This used to make lots ofproblems because that table got quite “hot”. This is why Oracle 10g has a special Optimizer for this Table.

    Greetings
    Bernd

  7. Along with Dov Wasserman, author of comment #5, I wonder how the name “Dual” was selected. We might ask where the name was first used and who were the contributors. Let’s keep looking for an answer.

    Good luck!

    Norman

  8. hi
    its a beautiful sight,my question is iam haveing a column named sex

    in which a subdivsion male and female catgeory is there and i have

    some memers underit how can i retrive the male and female seperately

    in a single statment.

  9. Select sex
    from (table name)
    order by sex ASC;

    This will separate M from F

    Otherwise if you want just M (or just F)

    Select sex
    from (table name)
    where sex = ‘M’;

Leave a Reply

Your email address will not be published. Required fields are marked *