Settings for Temporary Tablespaces ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Settings for Temporary Tablespaces ?

  1. #1
    Join Date
    Jun 2002
    Posts
    11

    Exclamation

    Hi,everybody.
    Our Temporary TS is 300 MB size,INITIAL=NEXT=SORT_AREA_SIZE = 1MB,with 2 Datafiles of 150 MB (each)and the AUTOEXTENT disabled. Have NT 4 and RAID 5.

    PROBLEM we have: we are receiving occasionally,several times a day the message:
    "ORA-1652:unable to extend temp segment by 128 in tablespace TEMPORARY_DATA"

    NEED YOUR OPINION ABOUT:
    I'd like to know,if the followings features are right when you define a Temporary TS:
    1. The INITIAL=NEXT and must be equal or multiple of sort_area_size ?
    2. Must MAXEXTENTS "have a limit" or be "Unlimited" ?
    3. Is right to enable AUTOEXTENT for the datafiles of the Temp TS or give them a fixed size? What's better ?
    If AUTOEXTENT is used, when the instance is shutdown and then startup,these datafiles return to their original sizes?

    What is your best reccomendation for me ?

    Thanks in advance.

  2. #2
    Join Date
    Nov 2000
    Posts
    224
    You are right on No. 1.

    Doesn't matter to have maxextent or unlimited as nothing is permanent in that Tablespace. Better to have high MAXEXTENTS.

    Autoextend is alright as long as you have MAXSIZE of some value.

    I think the file size will not reduce after you recycle the database, but I have Never Tried it.

  3. #3
    Join Date
    Sep 2002
    Posts
    5

    Wink

    ORA-01652 unable to extend temp segment by string in tablespace string
    Cause: Failed to allocate an extent for temporary segment in tablespace.
    Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.


    Duration creation of Temporary Tablespac

    Uniform size= sort_area_size * n
    Ajmal Chishti

  4. #4
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Originally posted by MMCP
    Hi,everybody.
    Our Temporary TS is 300 MB size,INITIAL=NEXT=SORT_AREA_SIZE = 1MB,with 2 Datafiles of 150 MB (each)and the AUTOEXTENT disabled. Have NT 4 and RAID 5.

    PROBLEM we have: we are receiving occasionally,several times a day the message:
    "ORA-1652:unable to extend temp segment by 128 in tablespace TEMPORARY_DATA"


    What is your best reccomendation for me ?

    Thanks in advance.
    What is the size of your database and size of frequently used segments??? and what are the queries that cause huge amount of sort segments??? If the size of temp tablespace is comparable to other sizes (db and segment sizes), then tune SQL queries. If temp tablespace is very small compared with db size, then increase the size of temp tablespace either by adding a tempfile or increase the size of existings files... the conclusion is... "it depends" ... solution depends on your system..
    -nagarjuna

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