查看临时表空间的使用情况

Temporary Tablespaces 
  Data that is only used for the duration of a session is stored in a temporary tablespaces. Such data is for example the result of a sort (order by) operation. More specifically, the date is held in temporary segments. It is normal if temporary tablespaces appear full after a while. This is because the extents are not managed in the data dictionary but in memory. The reason is simple: updating the data dictionary would be an expensive operation. v$sort_usage and v$sort_segment can be used to find out who occupies the space in temporary Tablespaces. 

--查看谁在表空间临时表空间上做什么

SELECT se.username,
       se.sid,
       su.extents,
       su.blocks * to_number(rtrim(p.VALUE)) AS Space,
       su.tablespace,
       su.segtype,
       s.sql_text 　　
  FROM v$sort_usage su, v$parameter p, v$session se, v$sql s
 WHERE p.NAME = 'db_block_size'
   AND su.session_addr = se.saddr
   AND s.hash_value = su.sqlhash
   AND s.address = su.sqladdr 　　
 ORDER BY se.username, se.sid;

 

V$SORT_USAGE
Describes sort usage.

Column	Datatypes	Description
USERNAME	VARCHAR2(30)	User who requested temporary space
USER	VARCHAR2(30)	User who requested temporary space
SESSION_ADDR	RAW(4)	Address of shared SQL cursor
SESSION_NUM	NUMBER	Serial number of session
SQLADDR	RAW(4)	Address of SQL statement
SQLHASH	NUMBER	Hash value of SQL statement
TABLESPACE	VARCHAR2(31)	Tablespace in which space is allocated
CONTENTS	VARCHAR2(9)	Indicates whether tablespace is TEMPORARY/PERMANENT
SEGTYPE	VARCHAR2(9)
SEGFILE#	NUMBER	File number of initial extent
SEGBLK#	NUMBER	Block number of the initial extent
EXTENTS	NUMBER	Extents allocated to the sort
BLOCKS	NUMBER	Extents in blocks allocated to the sort
SEGRFNO#	NUMBER	Relative file number of initial extent

   

v$sort_segment

This view contains information about every sort segment in a given instance. The view is only updated when the tablespace is of the TEMPORARY type.

Column	Datatype	Description
TABLESPACE_NAME	VARCHAR2(31)	Name of tablespace
SEGMENT_FILE	NUMBER	File number of the first extent
SEGMENT_BLOCK	NUMBER	Block number of the first extent
EXTENT_SIZE	NUMBER	Extent size
CURRENT_USERS	NUMBER	Number of active users of the segment
TOTAL_EXTENTS	NUMBER	Total number of extents in the segment
TOTAL_BLOCKS	NUMBER	Total number of blocks in the segment
USED_EXTENTS	NUMBER	Extents allocated to active sorts
USED_BLOCKS	NUMBER	Blocks allocated to active sorts
FREE_EXTENTS	NUMBER	Extents not allocated to any sort
FREE_BLOCKS	NUMBER	Blocks not allocated to any sort
ADDED_EXTENTS	NUMBER	Number of extent allocations
EXTENT_HITS	NUMBER	Number of times an unused extent was found in the pool
FREED_EXTENTS	NUMBER	Number of deallocated extents
FREE_REQUESTS	NUMBER	Number of requests to deallocate
MAX_SIZE	NUMBER	Maximum number of extents ever used
MAX_BLOCKS	NUMBER	Maximum number of blocks ever used
MAX_USED_SIZE	NUMBER	Maximum number of extents used by all sorts
MAX_USED_BLOCKS	NUMBER	Maximum number of blocks used by all sorts
MAX_SORT_SIZE	NUMBER	Maximum number of extents used by an individual sort
MAX_SORT_BLOCKS	NUMBER	Maximum number of blocks used by an individual sort
RELATIVE_FNO	NUMBER	Relative file number of the sort segment header

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22514512/viewspace-614612/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22514512/viewspace-614612/