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

```select (s.tot_used_blocks/f.total_blocks)*100 as "percent used" from
(select sum(used_blocks) tot_used_blocks from v\$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks from dba_data_files where tablespace_name='TEMP') f;
```

```select (s.tot_used_blocks/f.total_blocks)*100 as "percent used" from
(select sum(used_blocks) tot_used_blocks from v\$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;
```

``` select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
2  from (select sum(used_blocks) tot_used_blocks from
v\$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;

percent used
------------
0
```

1. Maclean says
```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
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
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
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  #*****************************************************

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#,