add a datafile for temp tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: add a datafile for temp tablespace

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Posts
    449

    add a datafile for temp tablespace

    How would you add a new datafile to this tablespace?
    Originally created and successful:

    CREATE temporary TABLESPACE DT_TEMP
    tempfile '/u02/dbs/RIDEV/temp01.dbf' SIZE 320M
    extent management local
    uniform size 8M

    SQL> select * from dba_temp_files;

    FILE_NAME
    --------------------------------------------------------------------------------
    FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
    ---------- ------------------------------ ---------- ---------- ---------
    RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
    ------------ --- ---------- ---------- ------------ ---------- -----------
    /u02/dbs/RIDEV/temp01.dbf
    1 DT_TEMP 335544320 20480 AVAILABLE
    1 NO 0 0 0 327155712 19968


    Now I am saying:
    1* alter tablespace dt_temp add datafile '/u05/dbs/RIDEV/temp02.dbf' size 1000M
    SQL> /
    alter tablespace dt_temp add datafile '/u05/dbs/RIDEV/temp02.dbf' size 1000M
    *
    ERROR at line 1:
    ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
    SQL> c/alter/alter temp/
    1* alter temp tablespace dt_temp add datafile '/u05/dbs/RIDEV/temp02.dbf' size 1000M
    SQL> /
    alter temp tablespace dt_temp add datafile '/u05/dbs/RIDEV/temp02.dbf' size 1000M
    *
    ERROR at line 1:
    ORA-00940: invalid ALTER command


    SQL> c/temp/temporary/
    1* alter temporary tablespace dt_temp add datafile '/u05/dbs/RIDEV/temp02.dbf' size 1000M
    SQL> /
    alter temporary tablespace dt_temp add datafile '/u05/dbs/RIDEV/temp02.dbf' size 1000M
    *
    ERROR at line 1:
    ORA-00940: invalid ALTER command

    I cant use resize since the filesystem is almost full..

    Thanks, ST2000

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    alter tablespace temp add tempfile

    look sql reference in tahiti.oracle.com

  3. #3
    Join Date
    Oct 2000
    Posts
    449
    Thanks Pando.. I'm back in business..

  4. #4
    Join Date
    Oct 2000
    Posts
    449
    Just another observation..

    After I added another 2GB successfully to this temp tablespace, there is no change in the file system space and secondly when I checked the tablespace listing listing in TOAD, it shows as 100% full.. I checked to see if any users using the temp_segment and it's none..

    Thanks, ST2000

  5. #5
    Join Date
    Oct 2000
    Posts
    449
    Just another observation..

    After I added another 2GB successfully to this temp tablespace, there is no change in the file system space and secondly when I checked the tablespace listing listing in TOAD, it shows as 100% full.. I checked to see if any users using the temp_segment and it's none..

    Thanks, ST2000

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    temp tablespace always show full

    in tahiti.oracle.com read concepts about temporary tablespaces

  7. #7
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    You can add files very easily with Enterprise Manager.
    David Knight
    OCP DBA 8i, 9i, 10g

  8. #8
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    You may want to add Additional Datafile only if there is a real need. Did you get any error? Otherwise it always shows as full.
    -- Dilip

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