4 Replies Latest reply: May 8, 2008 4:12 AM by Pavan Kumar RSS

    Create Tablespace

    629537
      Hi all, can someone tell what happens when i issue a command:

      "Create tablespace temp"
        • 1. Re: Create Tablespace
          APC
          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
            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
              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
                Pavan Kumar
                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