OraFAQ Forum: SQL & PL/SQL » TEMP table space

Home » SQL & PL/SQL » SQL & PL/SQL » TEMP table space (Oracle 9i, AIX 5.1)

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

TEMP table space [message #407513]

Wed, 10 June 2009 06:33

senaubi_me
Messages: 24
Registered: February 2008

Junior Member

Hi,

I would just like to ask if using oracle hints would contribute a tremendous increase in TEMP table space? Especially when using NO_INDEX?

Thank You.

Report message to a moderator

Re: TEMP table space [message #407515 is a reply to message #407513]

Wed, 10 June 2009 06:37

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It depends.
It can be more, less or the same thing.

Regards
Michel

Report message to a moderator

Re: TEMP table space [message #407517 is a reply to message #407515]

Wed, 10 June 2009 06:45

senaubi_me
Messages: 24
Registered: February 2008

Junior Member

Hi Michel,

Thank you for the reply.

I would just like to clarify, what do you mean by it can be more, less or same thing?

Regards,

Report message to a moderator

Re: TEMP table space [message #407525 is a reply to message #407517]

Wed, 10 June 2009 07:08

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

With or without any hint TEMP space usage can be more, less or same one than without or with any other hint.
So it can be "a tremendous increase".

Regards
Michel

[Updated on: Wed, 10 June 2009 07:08]

Report message to a moderator

Re: TEMP table space [message #407539 is a reply to message #407525]

Wed, 10 June 2009 07:38

senaubi_me
Messages: 24
Registered: February 2008

Junior Member

Hi,

Would just to ask what would be the probable cause of having a tremendous increase in temp table space that would result to ORA-01652:unable to extend temp segment by 257 in tablespace TEMP?

Thank You.

Report message to a moderator

Re: TEMP table space [message #407543 is a reply to message #407539]

Wed, 10 June 2009 07:44

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Executing an operation that uses temp space.

Regards
Michel

Report message to a moderator

Re: TEMP table space [message #407545 is a reply to message #407539]

Wed, 10 June 2009 07:45

karthick_arp
Messages: 13
Registered: February 2006
Location: hyderabad

Junior Member

Either you have not sized your temporary tablespace properly or some thing is eating a lot of it which need to be fixed.

Report message to a moderator

Re: TEMP table space [message #407547 is a reply to message #407539]

Wed, 10 June 2009 07:55

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

If the hint that you specified caused a lot more data to be sorted, then Temp space usage would go up.

If your hint caused a hash join to happen, and this join processed more data than could fit into memory, then Temp space usage would go up.

This question really is impossible to answer accurately with the information you've provided.

Report message to a moderator

Previous Topic:	way to Extract all table names from a program?
Next Topic:	counting within cursor loops (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Feb 14 16:32:57 CST 2025