ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 查看临时表空间的使用情况

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

原创 Linux操作系统 作者:oracle_lcs 时间:2009-09-14 15:39:21 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 栽培你的团队
请登录后发表评论 登录
全部评论

注册时间:2009-09-11

  • 博文量
    5
  • 访问量
    6891