11g临时表空间的几个数据字典视图含义
1、DBA_TEMP_FILES：描述了数据库的所有临时文件
| Column | Datatype | NULL | Description |
|---|---|---|---|
| FILE_NAME | VARCHAR2(513) | Name of the database temp file | |
| FILE_ID | NUMBER | File identifier number of the database temp file | |
| TABLESPACE_NAME | VARCHAR2(30) | NOT NULL | Name of the tablespace to which the file belongs |
| BYTES | NUMBER |
|
Size of the file (in bytes) |
| BLOCKS | NUMBER |
|
Size of the file (in Oracle blocks) |
| STATUS | CHAR(9) |
File status:
|
|
| RELATIVE_FNO | NUMBER | Tablespace-relative file number | |
| AUTOEXTENSIBLE | VARCHAR2(3) | Indicates whether the file is autoextensible (YES) or not (NO) | |
| MAXBYTES | NUMBER |
|
maximum size of the file (in bytes) |
| MAXBLOCKS | NUMBER |
|
Maximum size of the file (in Oracle blocks) |
| INCREMENT_BY | NUMBER | Default increment for autoextension (in Oracle blocks) | |
| USER_BYTES | NUMBER | Size of the useful portion of the file (in bytes) | |
| USER_BLOCKS | NUMBER | Size of the useful portion of the file (in Oracle blocks) |
这是联机文档展示的视图字段
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：显示了临时段的使用
| Column | Datatype | Description |
|---|---|---|
| USERNAME | VARCHAR2(30) | User who requested temporary space |
| USER | VARCHAR2(30) | This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value inUSERNAME. |
| SESSION_ADDR | RAW(4 | 8) | Address of shared SQL cursor |
| SESSION_NUM | NUMBER | Serial number of session |
| SQLADDR | RAW(4 | 8) | Address of SQL statement |
| SQLHASH | NUMBER | Hash value of SQL statement |
| SQL_ID | VARCHAR2(13) | SQL identifier of SQL statement |
| TABLESPACE | VARCHAR2(31) | Tablespace in which space is allocated |
| CONTENTS | VARCHAR2(9) | Indicates whether tablespace is TEMPORARY or PERMANENT |
| SEGTYPE | VARCHAR2(9) |
Type of sort segment:
|
| 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 |
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 | VARCHAR2(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;