Oracle dual table

Question

Is there any way to create / recreate dual table in oracle. It was accidentaly dropped from oracle database.

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: http://stackoverflow.com/questions/2816478/recovering-dual-table-in-oracle - added suggestions here.

Answer 2

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.