Hi,
oracle version : oracle 11g r2
os : linux
storage : asm on exadata
please help us to understand the temp usage
{code}
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> col USERNAME format a20
SQL> col SPID format a10
SQL> col MODULE format a30
SQL> col PROGRAM format a30
SQL> SELECT s.sid, s.serial#, s.username
2 ,p.spid, s.module, p.program
3 ,SUM(su.blocks) * tbsp.block_size/1024/1024 mb_used
4 ,su.tablespace
5 FROM v$sort_usage su
6 ,v$session s
,dba_tablespaces tbsp
7 8 ,v$process p
9 WHERE su.session_addr = s.saddr
10 AND su.tablespace = tbsp.tablespace_name
11 AND s.paddr = p.addr
12 GROUP BY
13 s.sid, s.serial#, s.username, s.osuser, p.spid, s.module,
14 p.program, tbsp.block_size, su.tablespace
15 ORDER BY s.sid;
SID SERIAL# USERNAME SPID MODULE PROGRAM MB_USED TABLESPACE
---------- ---------- -------------------- ---------- ------------------------------ ------------------------------ ---------- -------------------------------
2092 11 DBSNMP 2861 emagent_SQL_oracle_database oracle@test@youtele.com 8 TMP4
SQL> select * from v$temp_space_header;
TABLESPACE_NAME FILE_ID BYTES_USED BLOCKS_USED BYTES_FREE BLOCKS_FREE RELATIVE_FNO
------------------------------ ---------- ---------- ----------- ---------- ----------- ------------
TMP1 2 3.6418E+12 222274816 33554432 2048 1024
TMP3 4 3.1595E+11 19283968 75497472 4608 1024
TMP4 5 3.2257E+11 19687936 0 0 1024
TMP5 6 1.1283E+13 688638464 0 0 1024
{code}
why bytes_used is not reclaiming as only 8MB is using currently
Thanks
PGR