How and when does the queries uses the TEMP2 tablespace?
When you create TEMP2 tablespace and set it as the default TEMP tablespace in your database. You can set it like this:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2 ;
I'll suggest you read more about temporary tablespace here: Altering and Maintaining Tablespaceshttps://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm#i1013552
It depends. The normal case is someone has set temp as the default, then wants to change something like shrink it, so they create temp2, switch the db and users to temp2, wait for anything using temp to finish using it, then drop it, recreate it, and do the same thing in reverse. But you may have some other situation.
You can have different default temp tablespace for different schema's. Its not mandatory to use temp2 at database level only.
TEMPORARY TABLESPACE Clause
Use this clause to assign or reassign a tablespace or tablespace group for the user's temporary segments.
tablespaceto indicate the user's temporary tablespace.
tablespace_group_nameto indicate that the user can save temporary segments in any tablespace in the tablespace group specified by
- Pavan Kumar N
On top of what others have said.
Not sure if EBS uses Global Temporary Tables but they can be created with a specific temporary tablespace so watch out for those too.