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 andv$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_USAGEDescribes sort usage.
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