oracle 临时表空间使用率,脚本：监控临时表空间使用率

REM Listing of temp segments

SELECT A.tablespace_name tablespace, D.mb_total,

SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free

FROM v$sort_segment A,

(

SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total

FROM v$tablespace B, v$tempfile C

WHERE B.ts#= C.ts#

GROUP BY B.name, C.block_size

) D

WHERE A.tablespace_name = D.name

GROUP by A.tablespace_name, D.mb_total;

REM Temp segment usage per session

SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,

P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

COUNT(*) statements

FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P

WHERE T.session_addr = S.saddr

AND S.paddr = P.addr

AND T.tablespace = TBS.tablespace_name

GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,

P.program, TBS.block_size, T.tablespace

ORDER BY sid_serial;

How Do You Find Who And What SQL Is Using Temp Segments?

For 8.1.7 to 9.2:

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text

FROM v$session a, v$sort_usage b, v$sqlarea c

WHERE a.saddr = b.session_addr

AND c.address= a.sql_address

AND c.hash_value = a.sql_hash_value

ORDER BY b.tablespace, b.blocks;

For 10.1 and above:

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text

FROM v$session a, v$tempseg_usage b, v$sqlarea c

WHERE a.saddr = b.session_addr

AND c.address= a.sql_address

AND c.hash_value = a.sql_hash_value

ORDER BY b.tablespace, b.blocks;

REM #*****************************************************

REM #File Name: sort.sql

REM #

REM #Purpose: Report Sort Statistics

REM#

REM #*****************************************************

SELECT substr(vs.username,1,20) "db user",

substr(vs.osuser,1,20) "os user",

substr(vsn.name,1,20) "Type of Sort",

vss.value

FROM v$session vs,

v$sesstat vss,

v$statname vsn

WHERE (vss.statistic#=vsn.statistic#) AND

(vs.sid = vss.sid) AND

(vsn.name like '%sort%')

ORDER BY 2,3;

Creator of Sort Segment in Oracle 8 and above

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,

a.username, a.osuser, a.status

FROM v$session a,v$sort_usage b

WHERE a.saddr = b.session_addr

ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;