This discussion is archived
4 Replies Latest reply: May 8, 2008 2:12 AM by PavanKumar RSS

Create Tablespace

629537 Newbie
Currently Being Moderated
Hi all, can someone tell what happens when i issue a command:

"Create tablespace temp"
  • 1. Re: Create Tablespace
    APC Oracle ACE
    Currently Being Moderated
    can someone tell what happens when i issue a command:

    "Create tablespace temp"
    It throws an error:
    SQL> create tablespace temp
      2  /
    create tablespace temp
                         *
    ERROR at line 1:
    ORA-02199: missing DATAFILE/TEMPFILE clause


    SQL>
    Like, durr.

    Cheers, APC

    Blog : http://radiofreetooting.blogspot.com/
  • 2. Re: Create Tablespace
    604693 Newbie
    Currently Being Moderated
    When you issue the command
    SQL>create tablespace temp;
    , you get the following error:

    ERROR at line 1:
    ORA-02199: missing DATAFILE/TEMPFILE clause
  • 3. Re: Create Tablespace
    119642 Explorer
    Currently Being Moderated
    Depending on your storage you will need to specify a tempfile; also if you have a tablespace already TEMP for example you can set the DEFAULT to be the new TMP tablespace. Uniform size depends on the size of sorts you expect to occur in your temporary tablespace..
    I always set auto extend off for the temporary tablespace just in case a Cartesian join goes ballistic and consumes tones of space.

    Example.

    CREATE TEMPORARY TABLESPACE TMP
    TEMPFILE '/database/SID/data01/sid_tmp_01.dbf' SIZE 3000M AUTOEXTEND OFF
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 131072K
    SEGMENT SPACE MANAGEMENT MANUAL
    /
    ALTER DATABASE SID DEFAULT TEMPORARY TABLESPACE TMP
    /
  • 4. Re: Create Tablespace
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    Go through the ORACLE Doc's every thing are documented there ...what we have to do ...all are Basics...

    Please find the link for your reference.

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7003.htm#SQLRF01403

    Thanks
    Pavan Kumar N