Friends, many of us know about DUAL and its usage. Lets learn few things about it…

1) can we drop a dual table?

Ans: Yes. but it will have serious impact on the database functionality. so you should never do that

2) Can we create DUAL table if dropped?

Ans: Yes. use the following steps for the same…

SQL> DROP TABLE SYS.DUAL ;
 
Table dropped.
 
SQL> CREATE TABLE SYS.DUAL
  2  (
  3    DUMMY  VARCHAR2(1 BYTE)
  4  )
  5  TABLESPACE SYSTEM;
 
Table created.
 
SQL> CREATE PUBLIC SYNONYM DUAL FOR SYS.DUAL;
 
Synonym created.
 
SQL> GRANT SELECT ON SYS.DUAL TO PUBLIC WITH GRANT OPTION;
 
Grant succeeded.
 
SQL> INSERT INTO dual VALUES (‘X’);
 
1 row created.
 
SQL> SELECT * FROM dual;
 
D                                                                              
-                                                                              
X        

3) Can i create my own DUAL table which is having same functionality?

Ans: Yes. use below script which will create MYDUAL as another DUAL table.

CREATE OR REPLACE PROCEDURE replace_mydual (
 table_name_in IN VARCHAR2
)
IS
BEGIN
 BEGIN
    EXECUTE IMMEDIATE ‘DROP TABLE ‘ || table_name_in;
 EXCEPTION
    WHEN OTHERS THEN NULL;
 END;

 EXECUTE IMMEDIATE ‘CREATE TABLE ‘
                   || table_name_in
                   || ‘ (dummy VARCHAR2(1))’;

 EXECUTE IMMEDIATE
    ‘CREATE OR REPLACE TRIGGER mydual_’ || table_name_in ||
    ‘  BEFORE INSERT
       ON ‘ || table_name_in || ‘
    DECLARE
       PRAGMA AUTONOMOUS_TRANSACTION;
       l_count PLS_INTEGER;
    BEGIN
       SELECT COUNT (*)
       INTO   l_count
       FROM   ‘ || table_name_in || ‘;

       IF l_count = 1
       THEN
          raise_application_error
          ( -20000
          , ”The ‘ || table_name_in || ‘ table can only have one row.” );
       END IF;
    END;’;

 EXECUTE IMMEDIATE ‘BEGIN INSERT INTO ‘
                   || table_name_in
                   || ‘ VALUES (”X”); COMMIT; END;’;

 EXECUTE IMMEDIATE ‘GRANT SELECT ON ‘
                   || table_name_in
                   || ‘ TO PUBLIC’;

 EXECUTE IMMEDIATE ‘CREATE PUBLIC SYNONYM ‘
                   || table_name_in
                   || ‘ FOR ‘
                   || table_name_in;

 EXECUTE IMMEDIATE
    ‘CREATE OR REPLACE FUNCTION next_pky (seq_in IN VARCHAR2)
        RETURN PLS_INTEGER AUTHID CURRENT_USER
     IS
        retval PLS_INTEGER;
     BEGIN
        EXECUTE IMMEDIATE ”SELECT ” || seq_in
                      || ”.NEXTVAL FROM ‘ || table_name_in ||
                      ‘|| ”INTO retval;
        RETURN retval;
     END next_pky;’;

END replace_mydual;

TOM KYTE explained about this in one of his articles and as always its best…

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388

some more interesting part, why DUAL?
http://radiofreetooting.blogspot.com/2006/12/why-dual.html

All above ask your friend GOOGLE about this, lot of results will hit your door

HAPPY LEARNING…