Which Temporary Tablespace is used for sorts?

Written by Paweł Barut
This time I will write about "Which Temporary Tablespace is used for sorts" ?. I was not wondering about this much, as usually there is only one Temporary tablespace in DB. Lets assume situation, as show on picture:

• User A
• Assigned to Temporary Tablespace TEMP_A
• has table TA
  
• User B
• Assigned to Temporary Tablespace TEMP_B
• has table TB
• owns procedure PB (definer rights)
  
• Both users has access rights for all above object (Select on Tables and Execute on Procedure)
  

So let's discuss some situations:

1. User A runs query on tables TA or TB (or any other) - when disk sort is needed then tablespace TEMP_A is used
2. User A executes procedure PB. Procedure PB opens cursor on table TB (or TA or any other). If disk sort is required then tablespace TEMP_B is used.
   

For me it was bit surprising. Especially that I did not find anywhere in Oracle documentation description for this behaviour:

TEMPORARY TABLESPACE Clause
Specify the tablespace or tablespace group for the user's temporary segments.

I was expecting that all sort segments will be created in tablespace that is assigned to that user. I was hopping to solve one of my issues that way. But it occurred that, sort segment is created by user B, because procedure PB uses user B rights. It is reasonable, as this is consistent with granting access to objects, and temporary objects are treated the same way as permanent ones. On the other hand select is run for user A - shouldn't TEMP_A be used in all cases? What is your opinion on that?

Cheers Paweł

--
Related Articles on Paweł Barut blog:

• How to shrink Oracle Context Index
• Using Named Parameters within SQL
• Alternative Syntax for Insert
• Extracting Data to Excel using only SQL*Plus
• Rows to single string or aggregating strings