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;ENDLOOP 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;/
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; /