Problem solve Get help with specific problems with your technologies, process and projects.

When is temporary tablespace used?

When the Temporary Tablespace is used? Most documents say that it is used when "SORTS" can't be done fully in memory (order by, group by, union). Attending this, we tried to see what was happening with this query:

 SELECT s.username, u."USER", u.tablespace,u.contents,u.extents,
FROM v$session s,v$sort_usage u,v$sql a 
and a.hash_value = u.sqlhash and u.contents = 'TEMPORARY'
Our confusion is that the query is showing some users with this Sql_Text(among others):"SELECT NOMBRE,DIA_PROCESO FROM ARINCD WHERE NO_CIA = :b1 AND CENTRO = :b2",with extents between 110 and 115, also with #blocks between 2775 and 2850 (almost 22 MB each one!).


  1. Is the query used to determine what sql_texts use Temp TS wrong? Have you a better one?
  2. Why, when I run the Sql_Text in TOAD: "SELECT NOMBRE,DIA_PROCESO FROM ARINCD..." and then look V$SORT_USAGE, is it empty? No sort activity. Is there a difference between running a statement in SQL*Plus(TOAD) and a form ? Maybe this answer is related to question #1.
  3. Is the Temporary TS used for segments other than "SORTS" only? If so, do you have a query or script to see this?
Our block size= 8K, SORT_AREA_SIZE= 192K and the TS TEMPORARY= 240 M, with initial=next = 192K.

The v$sort_usage query you run is fine. Just remember that v$sort_usage shows currently active sorts for the instance. Therefore, you will only see information returned via this query if a sort is currently active (i.e. you do not see the history of all sorts that have occurred).

The following query can identify some statistics regarding the users performing sorts. This helps identify the particular sessions that may be issuing resource-intensive sorts. You can also determine whether an in-memory or disk sort is being performed. The following query lists the users performing sorts a well as the type of sort:

SELECT substr(vs.username,1,20) "db user",
       substr(vs.osuser,1,20)   "os user",
       substr(vsn.name,1,20)   "Type of Sort",
  FROM v$session vs, v$sesstat vss, v$statname vsn 
 WHERE (vss.statistic#=vsn.statistic#) 
   AND (vs.sid = vss.sid) 
   AND (vsn.name like '%sort%') 
 ORDER BY 2,3; 
The query you seem to be seeing frequently (SELECT NOMBRE,DIA_PROCESO....) must be a frequently run query (likely run within one application accessing the database). The reason why you don't see anything when you run the query in TOAD and then check v$sort_usage is because the query is over when you check....and as I mentioned earlier, v$sort_usage only shows currently active sorts.

If you want to see which objects (tables, indexes, etc) are stored in which tablespaces, you'll need to query dba_segments. It will show you a list of objects by name and type and by tablespace.

For More Information

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.