假如我們使用的臨時(shí)表空間是temoPRary的,就會(huì)在v$sort_segment中顯示,假如臨時(shí)表空是permanent的話,就不會(huì)在這個(gè)表中進(jìn)行顯示了. Code: [Copy to clipboard]SQL> select tablespace_name,extent_size,total_extents,total_blocks fromv$sort_segment; TABLESPACE_NAME EXTENT_SIZE TOTAL_EXTENTS TOTAL_BLOCKS------------------------------- ----------- ------------- ------------TEMP 128 5999 767872 視圖v$sort_usage顯示Oracle在如何使用和分配臨時(shí)段的. Code: [Copy to clipboard]SQL> select username,session_addr,sqladdr,sqlhash,segtype,2 segfile#,segblk#,extents,blocks
3 from v$sort_usage4 / USERNAME SESSION_ADDR SQLADDR SQLHASH------------------------------ ---------------- ---------------- ----------SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS--------- ---------- ---------- ---------- ----------AICBS C0000000D7EF99E8 C0000000E1BFE970 4053158416SORT 1001 379145 231 29568 可以根據(jù)session_addr查詢v$session得到當(dāng)前登錄用戶的session信息, 可以通過sqladdr和sqlhash查詢v$sqltext查詢這條語句的具體信息 下面的語句可以比較好的監(jiān)控正在使用的臨時(shí)段的信息
Code: [Copy to clipboard]select se.username,se.sid,su.extents,su.blocks * to_number(rtrim(p.value)) as Space,tablespace,segtype,sql_textfrom v$sort_usage su,v$parameter p,v$session se,v$sql swhere p.name = 'db_block_size'and su.session_addr = se.saddrand s.hash_value = su.sqlhash and s.address = su.sqladdrorder by se.username, se.sid/
