Oracle: ORA-01652: unable to extend temp segment by 256 in ...
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
Those tablespace are DMTT and we running
Oracle 184.108.40.206 on windows 2000 Advanced
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?
Last edited by Ablakios; 09-07-2003 at 09:59 PM.
The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.
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...
An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.
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
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 )
"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"