Tablespace Administration - 3

Temporary tablespace can be monitored/modified similarly, as we do for usual tablespaces. Use below links for details.
Tablespace Administration
Tablespace Administration with ASM
The difference here is the segments are temporary and are not being tracked by the views we have for other tablespaces.
For the files we have dba_temp_files and v$tempfile, and files are represented as tempfile at the place of datafile, and v$temp_space_header keeps track of free space.

So for the space and Usage we can have a query like below:-


set linesize 120
select dt.Tablespace_Name "TableSpaceName", dt.Total "Total-MB", th.Used "Used-MB", th.Free "Free-MB"
from
(select Tablespace_name,  sum(Bytes/1024/1024) as Total from dba_temp_files group by tablespace_name) dt,
(select TableSpace_Name, sum(Bytes_Used/1024/1024) as Used, sum(Bytes_Free/1024/1024) as Free from gv$temp_space_header group by TableSpace_Name) th
where
dt.Tablespace_name=th.Tablespace_name;


For file details please use below query:-

set linesize 120
column File_Name format a45
select File_Name, maxbytes/1024/1024 as Max_MB, round(bytes/1024/1024) as Total_MB,
round(user_bytes/1024/1024) as Allocated_MB, autoextensible as AutoEx, Increment_by as Incr_By
from dba_temp_files where tablespace_name = '&tablespace_name';

For adding resizing tempfiles:-

alter tablespace TEMP add tempfile '<File Name>' size 100M;
alter database tempfile '<FileName>' Resize 1500M;

Dropping a Temp File:-

alter database tempfile '<File Name>' offline;
alter database tempfile '<File Name>' drop including datafiles;


For checking the sort usage, and the free space. And details of session which is causing too much sort we can use below queries.

Finding total sort usage:-

column TOTAL_MB format 999,999,999
column USED_MB format 999,999,999
column FREE_MB format 999,999,999
select vss.Tablespace_name, (vss.total_blocks * tbs.block_size)/1024/1024 "TOTAL MB", (vss.used_blocks * tbs.block_size)/1024/1024 USED_MB,
(vss.free_blocks * tbs.block_size)/1024/1024 FREE_MB
from
gv$sort_segment vss,
(SELECT   distinct vts.name, vtf.block_size
FROM     v$tablespace vts, v$tempfile vtf
WHERE    vts.ts#= vtf.ts# ) tbs
where tbs.name=vss.tablespace_name;

Sort usage by session:-

set linesize 150
column Tablespace_Name format a18
column Username format a13
column osuser format a13
column event format a30
column used_mb format 9,999,999
select vsu.tablespace, vsu.blocks, vs.sid, vs.serial#, vs.username, vs.osuser, vs.status, vs.event,
round((vsu.blocks * tbs.block_size)/1024/1024) Used_MB
from
v$session vs, v$sort_usage vsu,
(SELECT   distinct vts.name, vtf.block_size
FROM     v$tablespace vts, v$tempfile vtf
WHERE    vts.ts#= vtf.ts# ) tbs
where
vs.saddr = vsu.session_addr
and rownum <= 5
order by 2 desc;

The query shows the session details, event, sort usage in MB for top five sessions according to usage. Later you can dig into the session and find out the issue/solution.


If the sort usage are high then we assume two main reasons.
1. Bad query causing too much sort
2. High data volume
For first cause increasing the temporary tablespace can be a temporary remedy for the situation, better option always is to tune the query.