linux temp表空间在哪里,查看临时表空间的使用情况

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