What happens if you drop your DUAL table? [Oracle 9i] [Oracle 10g][Oracle 11g]

The dual table is a table which contains just one column and one row with the letter X.

All users of the database have access to the dual table but it should not be fiddled with. It is used for functions like

SQL> select sysdate from dual;

SYSDATE

---------

01-NOV-08

Just incase you happen to drop the dual table, it can be easily recreated with the following script.

Login as sysdba and execute:

CREATE TABLE SYS.DUAL

(

DUMMY VARCHAR2(1)

)

TABLESPACE SYSTEM;

drop PUBLIC SYNONYM DUAL;

CREATE PUBLIC SYNONYM DUAL FOR SYS.DUAL;

GRANT SELECT ON SYS.DUAL TO PUBLIC WITH GRANT OPTION;

INSERT INTO dual VALUES ('X');

SELECT * FROM dual;

commit;

That’s it!!! You have your DUAL table back.

Absence of DUAL table is likely to throw the following error to users (not sysdba users)

SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.