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)
- User A runs query on tables TA or TB (or any other) - when disk sort is needed then tablespace TEMP_A is used
- 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.
TEMPORARY TABLESPACE ClauseI 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?
Specify the tablespace or tablespace group for the user's temporary segments.
Related Articles on Paweł Barut blog: