Oracle: ORA-01652: unable to extend temp segment by 256 in ...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Oracle: ORA-01652: unable to extend temp segment by 256 in ...

  1. #1
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372

    Oracle: ORA-01652: unable to extend temp segment by 256 in ...

    Hi,
    Users are getting the following error msg.
    Oracle: ORA-01652: unable to extend temp segment by 256 in tablespace TEMP1...

    We have a reporting application that is accessed by 3000 users.

    Because of that I have created a dedicated TEMP1 tablespace just for the application and another tablespace TEMP for the users.

    I received ORA-01652 and I have added datafiles to both tablespaces.
    TEMP1 is now up to 8GB
    TEMP is now 7 GB.
    I also increased the SORT_ARAE_SIZE and SORT_AREA_RETAINED_SIZE to 1048576 and keep coalescing both tablespaces. Their pctincrease is set to 0.and I am STILL
    GETTING ORA-01652.

    Those tablespace are DMTT and we running
    Oracle 8.1.7.4 on windows 2000 Advanced
    server.

    I think 15GB for total temp tablespace is
    way too Big and I feel that I just can't keep adding and adding datafiles. Is there anything you can advise me to do?
    Thanks
    Last edited by Ablakios; 09-07-2003 at 09:59 PM.
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Is one Mb big enough for your sort area size? If not everything is going to go to temp

  3. #3
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    Hey Dave,
    Just curious. how big is your sort_area_size? and how big can it be?
    Please advise
    Thanks
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    mine is 10Mb - but then again my application does a truck load of sorting. Not sure of the maximum size, probably quite large

    Need to tune it for yourself, try increasing it to 5 Meb and see what happens (if you have enough RAM)

  5. #5
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Maybe a stupid question, but ... are you shure that the temporary tablespace was created with "create temporary tablespace..." (so it isn't a permanent tablespace? Otherwise it keeps on growing and growing...)

    DMTT means "Data dictionary Managed Temporary Tablespace"? In that case, why don't you use Locally Managed Temporary Tablespaces? You then can simply use uniform extents of the size of your sort_area_size (or a multiple of that size). Then you don't have to coalesce...

    HTH,
    Erik
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  6. #6
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    It is a temporary tablespace. I used to have LMTT but I had a bad experience with it and you cannot change the storage clause.You can deallocate DMTT maually. Again this is a DMTT.
    thanks
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Rather than coaleseing manually, why dont you keep your PCTINCREASE > 0
    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"

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Because with pctincrease > 0 you start get all funny extent sizes and not multiple of sort_area_size

    I dont see why you need to coalesce if you set your initial = next? Also what's the bad experience? We have been using locally managed temporary tablespace in production for over 2 years with no problems....

    Also check your queries, you might have some queries using sort merge join or carteasian product

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Pando :

    I didnt read post properly, just saw the "coalesed manully" part of the post, so suggested to have pctincrease thinking it were Perm TS ( but ofcourse all segments in it shud be pctincrease 0, it will only help then )
    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"

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