Oracle 9i: Temp Tablespace Usage Monitoring

-- Create the monitoring Table
create table ztbl_temp_monitor
(snap_time date default sysdate,
Total_Size_MB int,
Used_Size_MB int,
Free_Size_MB int,
Tablespace_name varchar(30)
);

-- Insert command for data
insert into ztbl_temp_monitor(Tablespace_name, Total_Size_MB, Used_Size_MB, Free_Size_MB)
select b.tablespace_name,
b.SizeMB,
nvl(a.UsageMB, 0) as UsageMB,
b.SizeMB - nvl(a.UsageMB, 0) as FreeMB
from (select sum(BLOCKS) * b.VALUE / 1024 / 1024 as UsageMB,
tablespace as tablespace_name
from v$tempseg_usage a, v$parameter b
where b.name = 'db_block_size'
group by TABLESPACE, b.VALUE) a,
(select sum(bytes) / 1024 / 1024 as SizeMB, tablespace_name
from dba_temp_files
group by tablespace_name) b
where a.tablespace_name(+) = b.tablespace_name;

-- Create a procedure to incorporate the insert command
CREATE OR REPLACE PROCEDURE zproc_temp_usage_monitor IS
BEGIN
insert into ztbl_temp_monitor(Tablespace_name, Total_Size_MB, Used_Size_MB, Free_Size_MB)
select b.tablespace_name,
b.SizeMB,
nvl(a.UsageMB, 0) as UsageMB,
b.SizeMB - nvl(a.UsageMB, 0) as FreeMB
from (select sum(BLOCKS) * b.VALUE / 1024 / 1024 as UsageMB,
tablespace as tablespace_name
from v$tempseg_usage a, v$parameter b
where b.name = 'db_block_size'
group by TABLESPACE, b.VALUE) a,
(select sum(bytes) / 1024 / 1024 as SizeMB, tablespace_name
from dba_temp_files
group by tablespace_name) b
where a.tablespace_name(+) = b.tablespace_name;
   COMMIT;
END zproc_temp_usage_monitor;
/

-- Create and schedule a Job for each 2 minutes to fire the query
declare
   jobno number;
begin
   dbms_job.submit( jobno,
                   'begin zproc_temp_usage_monitor; end;',
                    INTERVAL => 'SYSDATE+(2/24/60)'
                    );
end;
/

-- Finally check the monitoring Table
select to_char(snap_time, 'DD-MON-YY HH24:MI:SS') snap_time, total_size_mb, used_size_mb, free_size_mb, tablespace_name from ztbl_temp_monitor;

-- When Finished just make broken to that job
exec dbms_job.broken(5,true);


 

-- To monitor other tablespaces' usage

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;
 

Reference:
http://toddlerdba.blogspot.com.au/2011/05/temporary-tablespace-usage.html