[转]Temp表空间的分配

原文地址:http://space.itpub.net/10248702/viewspace-660656,测试部分请看原文。

1. Temp表空间的分配与回收机制?
贪心法分配机制. 使用完后释放Extents,但仍表示已分配.
下一个Session要用时,先从已分配的空间中,先使用,只有不够时,才又再重新申请分配新的Extents.
直到没有Extent可用,则报错:ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
这其实就是Extent的分配机制,只是在临时表空间上的应用而已.

 2. 哪些动作会占用到temp表空间
DISK Sort, Temporary Table, Direct write/read path. Create table xx as select * from xx;

3. 临时表空间的相关视图:

3.1 临时表空间的基本信息
dba_tablespaces

select tablespace_name, block_size,initial_extent, next_extent, pct_increase, MAX_EXTENTS,status,extent_management,SEGMENT_SPACE_MANAGEMENT  
from dba_tablespaces where tablespace_name='TEMP';

3.2 临时文件的信息
dba_temp_files 

col file_name format a40;
select file_name, file_id, tablespace_name, bytes/1024/1024,autoextensible, maxbytes/1024/1024, user_bytes/1024/1024 from dba_temp_files;

V$TEMPFILE

col name format a40;
select FILE#, NAME,BYTES/1024/1024,STATUS from V$TEMPFILE;

3.3 查看临时文件使用率 V$TEMP_SPACE_HEADER
This view displays aggregate information per file per LOCALLY MANAGED temporary tablespace regarding how much space is currently being used and how
much is free as identified in the space header.

select TABLESPACE_NAME,file_id,
(bytes_used+bytes_free)/1024/1024 "size_total(mb)",
bytes_used/1024/1024,
bytes_free/1024/1024
from V$TEMP_SPACE_HEADER;

bytes_used应理解为用过的.bytes_free应理解为没用过的.

3.4 查看正在使用的sort segment V$SORT_SEGMENT

select tablespace_name, CURRENT_USERS,TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS
from v$sort_segment;

3.5 查看哪个用户的哪个语句在使用sort segment,使用了多少, V$SORT_USAGE=v$tempseg_usage

select a.username, a.SESSION_NUM,b.sql_text, a.segfile#, a.segblk# "Begin block number", a.extents , a.blocks, a.TABLESPACE
from v$tempseg_usage a, v$sqlarea b
where a.SQLHASH = b.hash_value;

Comments are closed.