11g临时表空间的⼏个数据字典视图含义
1、DBA_TEMP_FILES：描述了数据库的所有临时⽂件
这是联机⽂档展⽰的视图字段
select * from dba_temp_files;
结合实际我们看下所有字段含义：FILE_NAME临时⽂件的名字包含路劲的；FILE_ID临时⽂件的识别号从1往下排；
TABLESPACE_NAME数据⽂件所属的表空间名如：temp；
BYTES此⽂件的⼤⼩以byte计算；BLOCKS此⽂件的⼤⼩以块计算；BYTES=BLOCKS*BD_BLOCK_SIZE;
select value from v$parameter t where t.NAME='db_block_size';
VALUE
--------------------------
8192
34358689792=4194176*8192
STATUS代表临时⽂件的状态，在11g⾥包含三个状态OFFLINE，ONLINE，UNKNOWN;AUTOEXTENSIBLE是否⽀持⾃动扩展，YES和NO两个状态，⾃动扩展的就有最⼤值和最⼩值；MAXBYTES⽂件的最⼤⼤⼩以byte计算；MAXBLOCKS⽂件的最⼤⼤⼩以块计算；⼆者之间的关系和刚才的BYTES和BLOCKS⼀样的；INCREMENT_BY每次⾃动扩展的块数；USER_BYTES可⽤的⽂件⼤⼩以byte计算；USER_BLOCKS可⽤的⽂件⼤⼩以块计算；
临时⽂件中有⼀部分是被系统占⽤的，像⽂件头信息等，这⼀部分的⼤⼩就等于（BYTES-USER_BYTES）/8192=BLOCKS-
USER_BLOCKS=128;
2、V$SORT_SEGMENT：显⽰给定实例的每个排序段的信息，只有发⽣在临时表空间的操作才会更新该视图。这⾥不仅仅记载排序动作，只要在临时表空间操作就会记录，⽽且排序发⽣在内存，也不会更新该视图的。
Column Datatype Description
TABLESPACE_NAME VARCHAR2(31)Name of the 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
select t.EXTENT_SIZE,
t.CURRENT_USERS,
t.TOTAL_EXTENTS,
t.TOTAL_BLOCKS,
t.USED_EXTENTS,
t.USED_BLOCKS,
t.FREE_EXTENTS,
t.FREE_BLOCKS
from v$sort_segment t;
EXTENT_SIZE为区的块数，128个块也就是1M；CURRENT_USERS当前使⽤临时表空间的⽤户数为5个；TOTAL_EXTENTS总共有116658个区；TOTAL_BLOCKS总共有的块数=TOTAL_EXTENTS*EXTENT_SIZE=116658*128=14932224；USED_EXTENTS已经使⽤的区数为5；USED_BLOCKS已经使⽤的块数=5*128=640；FREE_EXTENTS=TOTAL_EXTENTS-USED_EXTENTS；
3、V$TEMPSEG_USAGE：显⽰了临时段的使⽤
select t.USERNAME,t.SESSION_NUM,t.SQL_ID,t.TABLESPACE,t.CONTENTS,t.SEGTYPE,t.EXTENTS,t.BLOCKS from
v$tempseg_usage t;
查询出的数据正如V$SORT_SEGMENT查询出的结果，5个⽤户在使⽤临时段（USERNAME），使⽤了5个EXTENTS。此视图就体现了哪些⽤户在使⽤临时段，使⽤了多少
。⾄于SQL_ID体现的是不是正在消耗临时段的语句有待考证。
4、DBA_TEMP_FREE_SPACE：描述了表空间级别的临时表空间使⽤的信息
Column Datatype NULL Description
TABLESPACE _NAME VARCHAR
2(30)
NOT
NULL
Name of the tablespace
TABLESPACE
_SIZE
NUMBER Total size of the tablespace, in bytes
ALLOCATED_ SPACE NUMBER
Total allocated space, in bytes, including space that is currently allocated and used and space that
is currently allocated and available for reuse
FREE_SPACE NUMBER Total free space available, in bytes, including space that is currently allocated and available for reuse and space that is currently unallocated
TABLESPACE_SIZE：表空间总的⼤⼩；ALLOCATED_SPACE：已分配的总的表空间，包含当前分配的使⽤的空间以及当前分配的可以重⽤的空间；
FREE_SPACE：当前未分配的空间以及已分配可以重⽤的空间；
select * from dba_temp_free_space
通过结果我们可以看到TABLESPACE_SIZE= select sum(t.BYTES)from dba_temp_files t;ALLOCATED_SPACE⼤⼩与
v$temp_space_header的sum(t.bytes_used)⼤⼩是相同的；
视图包括哪几个视图⽬前还有很多疑惑，再续

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。