Check Temp tablespace usage in oracle 8i

For checking the temp used, allocation and free space, use the below query.

SQL> select ddf.tablespace_name, ddf.allocatedspace "AllocatedSpace(in GB)",
ds.usedspace "UsedSpace(in GB)",dfs.freespace "FreeSpace(in GB)",
round(((dfs.freespace/ddf.allocatedspace)*100),2) "FreeSpace%",round(((ds.usedspace/ddf.allocatedspace)*100),2) "UsedSpace%"
from
(select tablespace_name,round((sum(bytes)/1024/1024/1024),2) allocatedspace from dba_data_files group by tablespace_name)ddf,
(select tablespace_name,round((sum(bytes)/1024/1024/1024),2) usedspace from dba_segments group by tablespace_name)ds,
(select tablespace_name,round((sum(bytes)/1024/1024/1024),2) freespace from dba_free_space group by tablespace_name)dfs
where
ddf.tablespace_name=ds.tablespace_name and
ds.tablespace_name=dfs.tablespace_name and
ddf.tablespace_name='TEMP';


TABLESPACE_NAME                Allocated(in GB)  Used(in GB) Free(in GB)   Free%   Used%
------------------------------                   ---------------------    ---------------     ----------------   ----------   ----------
TEMP                                            1.95              .07                 1.88            96.41     3.59


* If you don't now the the temp tablespace name you can obtain it from dba_tablespaces.

To find out the which user using most temp space can be find by using the below query

SQL> SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks FROM v$session s, v$sort_usage u WHERE s.saddr=u.session_addr order by u.blocks desc;

USERNAME SID SERIAL# TABLESPACE CONTENTS EXTENTS BLOCKS 
------------------------------ ---------- ---------- ------------------------------- --------- ---------- ---------- 
SYS 156 36776 TEMP PERMANENT 61 7929 
BGAPP 176 61877 TEMP PERMANENT 1 129 
BGAPP 176 61877 TEMP PERMANENT 1 129

In the above result it clearly shows that most of the temp is used by the SYS user. So you have to find which sql statement consuming more temp by using the below query.

SQL> SELECT SQL_TEXT FROM V$SQLAREA WHERE (ADDRESS, HASH_VALUE) IN (SELECT SQL_ADDRESS, SQL_HASH_VALUE FROM V$SESSION WHERE SID= 156;

SQL_TEXT 

--------------------------------------------- 

analyze table OC.BKP_ODOMREADINGENTRY_2013010 

2 compute statistics

In this case the above sql statement consuming more temp space.