DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Dual table creation

  1. #1
    Join Date
    Sep 2002
    Posts
    376

    Dual table creation

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

    Regards
    Last edited by bang_dba; 07-04-2003 at 08:21 AM.

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Code:
    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
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    You might also want to:
    INSERT INTO DUAL VALUES('X');
    COMMIT;

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Why maxextents UNLIMITED. you know theres gonna be only one rec.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I remember a post where someone had managed to get TWO rows in dual - perhaps Amar is aiming high?

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by DaPi
    You might also want to:
    INSERT INTO DUAL VALUES('X');
    COMMIT;
    Ok i missed that out

    Thanks DaPi.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width