Home » Server Options » RAC & Failsafe » TEMP tablespace in RAC
TEMP tablespace in RAC [message #386107] Thu, 12 February 2009 12:47 Go to next message
nani_ar
Messages: 58
Registered: March 2006
Member
Hi,

I need some clarifications regarding the usage of TEMP tablespaces in Oracle 10gR2 RAC environment. There will be only 1 TEMP tablespace that will be used among the 2 instances.
As seen from the gv$sort_segment, the segments are not allocated uniformly across the 2 instances. In one instance it is 95% and 5% in the other. As such, I am getting the error 'Ora-01652 : Unable to extend temp segment by 128 in tablespace' and I m facing a serious performance issue.

Is there any way of controlling the amount of TEMP tablespace segments to be used for each instance?

Does any init.ora parameter setting affect this?


Kindly provide me the clarity regarding the usage of TEMP tablespace in RAC environment.


All the replies are welcome

Regards
Nani
Re: TEMP tablespace in RAC [message #386108 is a reply to message #386107] Thu, 12 February 2009 12:55 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Post the DDL of the temp tablespace you are using.
If Ora-01652 is raised for another user in another node
or
You believe there are multiple database users / schema's demanding for same temp resources,
You can create and assign multiple temp tablespaces.
Re: TEMP tablespace in RAC [message #386113 is a reply to message #386107] Thu, 12 February 2009 13:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Also,
there is a method to cleanup temp segments from the local instance.
If memory serves right,
you call it using
sql> alter session set events "....";

Check docs/metalink.
Re: TEMP tablespace in RAC [message #386177 is a reply to message #386113] Fri, 13 February 2009 00:35 Go to previous message
nani_ar
Messages: 58
Registered: March 2006
Member
Hi Mahesh,

Thanks for the information. We use tempfiles for the TEMP tablespace and there is only one TEMP tablespace for all the users across the 2 instances of RAC.

As mentioned by you, kindly let me know the process of cleaning up the temp segments for the local instance. You have mentioned

>alter system set events ......

Can you just explain more about cleaning the temp segments for the local instance.

Thanks,
Nani
Previous Topic: How to add VIP in existing RAC configuration
Next Topic: Error prko-2005. One instance of rac doesn't works
Goto Forum:
  


Current Time: Fri Mar 29 05:35:16 CDT 2024