Script for tablespace space monitoring for Windows Platform in Oracle Database

Script as PL/SQL anonymous block:

set feedback off
set serveroutput on
DECLARE
v_flag VARCHAR2(1) := 0;
v_date date;
BEGIN
Select sysdate into v_date from dual;
dbms_output.put_line('OUTPUT REPORT ON '||v_Date );
FOR loop_emp IN
(
select a.tablespace_name,
a.bytes_alloc/(1024*1024) "TOTALALLOC-MB",
a.physical_bytes/(1024*1024) "TOTALPHYSALLOC-MB",
nvl(b.tot_used,0)/(1024*1024) "USED-MB",
round((nvl(b.tot_used,0)/a.bytes_alloc)*100) "PERCENTUSED"
from
(select tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from dba_data_files
group by tablespace_name ) a,
(select
tablespace_name,
sum(bytes) tot_used
from dba_segments
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
and a.tablespace_name not in
(select distinct tablespace_name from dba_temp_files)
and a.tablespace_name not like 'UNDO%' order by 1
)

LOOP

If loop_emp.percentused > 70 then

dbms_output.put_line('Tablespace '||loop_emp.tablespace_name||' need to add more space.');
dbms_output.put_line('Space utilized in percentage: '||loop_emp.PERCENTUSED||'%');

v_flag := 1;
end if;
END LOOP loop_emp;

if v_flag = 0 then
dbms_output.put_line('Everything seems fine. Have Sufficient Space !!');
else
dbms_output.put_line('Please contact DBA team !!');
end if;
END;
/