Recreate the Oracle DUAL table

Question

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

Answer 1

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.