Dual table creation

[bang_dba]

Hi,
I happened to drop the DUAL table
How to re-create a dual table ?

Regards

[adewri]

CREATE TABLE DUAL
(
  DUMMY  VARCHAR2(1)
)
TABLESPACE SYSTEM
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          16K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL;


CREATE PUBLIC SYNONYM DUAL FOR DUAL;


GRANT SELECT ON  DUAL TO PUBLIC WITH GRANT OPTION;

HTH

[DaPi]

You might also want to:
INSERT INTO DUAL VALUES('X');
COMMIT;

[abhaysk]

Why maxextents UNLIMITED. you know theres gonna be only one rec.

[DaPi]

I remember a post where someone had managed to get TWO rows in dual - perhaps Amar is aiming high?

[abhaysk]

Originally posted by DaPi
I remember a post where someone had managed to get TWO rows in dual

Even after inserting new value into dual, oracle managed to retrive just one row, being smart by putting rownum=1 internally.
However in PL/SQL block it would fail with exact fetch returns more than one row

But if we create DUAL tab like this, we will get number of rows inserted, since oracle now thinks it as an user object.

[adewri]

Originally posted by DaPi
You might also want to:
INSERT INTO DUAL VALUES('X');
COMMIT;

Ok i missed that out

Thanks DaPi.