OraFAQ Forum: RAC & Failsafe » TEMP tablespace in RAC

Home » Server Options » RAC & Failsafe » TEMP tablespace in RAC

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

TEMP tablespace in RAC [message #386107]

Thu, 12 February 2009 12:47

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

Report message to a moderator

Re: TEMP tablespace in RAC [message #386108 is a reply to message #386107]

Thu, 12 February 2009 12:55

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: TEMP tablespace in RAC [message #386113 is a reply to message #386107]

Thu, 12 February 2009 13:14

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: TEMP tablespace in RAC [message #386177 is a reply to message #386113]

Fri, 13 February 2009 00:35

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 11:31:00 CST 2024