Temp表空间分配和回收测试.doc

Temp表空间的分配与回收测试 HYPERLINK /mon.php?action=viewspaceop=upitemid=660656uid上一篇 / HYPERLINK /mon.php?action=viewspaceop=nextitemid=660656uid下一篇 ?2010-04-24 19:53:10 / 个人分类： HYPERLINK spacelist-blog-itemtypeid-79213 Database HYPERLINK viewspace-660656 \l xspace-tracks 查看( 265 ) / HYPERLINK viewspace-660656 \l xspace-itemreply 评论( 0 ) / HYPERLINK viewspace-660656 \l xspace-itemform 评分( 0 / 0 ) HYPERLINK javascript:; \t _self Temp表空间的分配与回收机制测试 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_tablespacesselect 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$TEMPFILEcol name format a40;select FILE#, NAME,BYTES/1024/1024,STATUS from V$TEMPFILE; 3.3 查看临时文件使用率 V$TEMP_SPACE_HEADERThis view displays aggregate information per file per LOCALLY MANAGED temporary tablespace regarding how much space is currently being used and howmuch 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/1024from 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