Home » SQL & PL/SQL » SQL & PL/SQL » TEMP table space (Oracle 9i, AIX 5.1)
TEMP table space [message #407513] Wed, 10 June 2009 06:33 Go to next message
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.
Re: TEMP table space [message #407515 is a reply to message #407513] Wed, 10 June 2009 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends.
It can be more, less or the same thing.

Regards
Michel
Re: TEMP table space [message #407517 is a reply to message #407515] Wed, 10 June 2009 06:45 Go to previous messageGo to next message
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,
Re: TEMP table space [message #407525 is a reply to message #407517] Wed, 10 June 2009 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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.
Re: TEMP table space [message #407543 is a reply to message #407539] Wed, 10 June 2009 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Executing an operation that uses temp space.

Regards
Michel
Re: TEMP table space [message #407545 is a reply to message #407539] Wed, 10 June 2009 07:45 Go to previous messageGo to next message
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.
Re: TEMP table space [message #407547 is a reply to message #407539] Wed, 10 June 2009 07:55 Go to previous message
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.
Previous Topic: way to Extract all table names from a program?
Next Topic: counting within cursor loops (merged)
Goto Forum:
  


Current Time: Sat Dec 10 13:08:12 CST 2016

Total time taken to generate the page: 0.07165 seconds