The Oracle (tm) Users' Co-Operative FAQ

How do I find out who is currently using the space in the temporary tablespace ?


Author's name: Mark D Powell

Author's Email: Mark.Powell@eds.com

Date written: 17 August 2001
Revised: 13 August 2003

Oracle version(s): 6.0 - 9.2.0.1

How do I find out who is currently using the space in the temporary tablespace ?

Back to index of questions


Normally when the Oracle documentation talks about a temporary tablespace it is referring to a tablespace dedicated to holding sort segments. Technically speaking for versions 6.0 - 8.1.7 the temporary tablespace is the tablespace assigned to a user in the create user statement that is to be used to hold sort segments for the user and it can be any tablespace in the database. The default temporary tablespace is system:

	create user scott identified by tiger
	temporary tablespace temp
	default tablespace   user;

So I recommend that you always explicitly assign every user to a tablespace created expressly to hold temporary segments. Oracle also uses what are called temporary segments to facilitate certain operations such as direct loads and object extensions. All allocated temporary segments can be seen in the dba_extents dictionary view. With version 8.1 additional temporary segment types that are also associated to a temporary tablespace were added to support temporary tables and temporary LOB segments. The discussion is concerned only with temporary segments associated with sort operations.

For Oracle versions 6 - 7.2 all space allocation was controlled by obtaining the single ST lock on the database which single threads update access, if not all access, to the sys.fet$ and sys.uet$ base tables which underlay the dba_free_space and dba_extents dictionary views. To reduce contention for the ST lock Oracle devised a new way of managing temporary segments used for sorts so that the ST lock would not be necessary. Instead of allocating and deallocating temporary segments for every session that issues a sort request Oracle allocates only one sort segment per instance when the first sort is requested. This segment is then reused for all sort requests on the instance. Oracle knows to manage sort request this way based on the temporary tablespace being declared as type temporary:

	Create tablespace temp temporary datafile 'xxx'....

By default tablespaces are of type permanent. This new syntax/tablespace type was available with version 7.3 and with version 8.0 Oracle provided two new v$ views that provide access to sort segment information. Those views are v$sort_segment and v$sort_usage.

The view v$sort_segment will show one entry for each used tablespace of type temporary available to the instance. If will have no entries if the temporary tablespace is of type permanent.

The view v$sort_usage shows the internal allocation of the sort segment. SQL to format and display some of the contents follows. Notice the note in the script about a bug which causes the querying user to show as the owner for all extents in early versions of release 8.1.

rem
rem SQL*Plus script to display sort usage by user with join to v$session
rem to get session information (and correct user in some ver due to bug)
rem
rem  20010130  Mark D Powell   Saved version
rem  20020227  Mark D Powell   Add segtype to show temp tbl, hash use vs sort
rem
column tablespace format a12
column username   format a12

break on username nodup skip 1

select   se.username
        ,se.sid
        ,su.extents
        ,su.blocks * to_number(rtrim(p.value)) as Space
        ,tablespace
        ,segtype
from     v$sort_usage su
        ,v$parameter  p
        ,v$session    se
where    p.name          = 'db_block_size'
and      su.session_addr = se.saddr
order by se.username, se.sid
/

So far we can define either permanent or temporary, temporary tablespaces. To complicate matters a little more with version 8.1 Oracle introduced another variation to the mix, which also uses the term temporary. Tablespaces of either permanent or temporary mode always exist as part of the database and could require recovery in the event of media failure. To eliminate the need to back up a tablespace that holds no objects, except when they are in use, Oracle created a true temporary tablespace that exists only while the database is in use.

	create temporary tablespace temp
 	tempfile 'xxx';

Notice the new keyword tempfile and the placement of the keyword temporary before the tablespace name. When created in this manner the temporary tablespace will not exist when the database is shutdown, but will be automatically reallocated when the database is started. This eliminates the need to backup the temporary tablespace saving time and disk space.

You can find information on temporary data files in the v$tempfile and dba_temp_files views. It would appear that this newest method is Oracle's preferred method for defining sort space to Oracle.


Further reading: See the 8.1+ SQL Manual command create temporary tablespace and alter database for information on temporary tablespaces and files as well as the DBA Admin Manual chapter on Managing Tablespaces, section: Temporary Tablespaces.


Back to top

Back to index of questions