Recreate the Oracle DUAL table

Question

Is there any way to create / recreate dual table in Oracle? It was accidentally dropped.

Answer 1

Oracle becomes weird if DUAL is dropped accidentally.

SQLPlus says SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level. if there's no sys.DUAL or the current user has no privilege to select from it.

It feels like oracle determine version-related features according to DUAL.

Answer 2

You should probably contact Oracle Support.

Do you have a backup? If so, restore the table from your backup. Otherwise (and if contacting Oracle is not an option for you)...

Oracle has special optimizations for DUAL, but I don't know if there's anything special about the table itself. I would just treat it like a normal table and see what happens. Try this:

Connect as SYSDBA and then execute these commands:

CREATE TABLE SYS.DUAL
(
  DUMMY  VARCHAR2(1 BYTE)
);

INSERT INTO SYS.DUAL VALUES ( 'X' );

COMMIT;

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

CREATE PUBLIC SYNONYM DUAL FOR SYS.DUAL;

And never EVER change ANYTHING in the SYS schema again!

EDIT: Just noticed a duplicate from TODAY: https://stackoverflow.com/questions/2816478/recovering-dual-table-in-oracle - added suggestions here.

Answer 3

Dual Table is a dummy table in system tablespace. As a user, u dont have right to drop it. if you have your own dual table in your schema, u can play around it. U can perform dml, drop it, truncate it etc. Even if u drop, u can recreate it again from the same scripts as previous.