set feedback offset serveroutput onDECLAREv_flag VARCHAR2(1) := 0;v_date date;BEGINSelect 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_allocfrom dba_data_filesgroup by tablespace_name ) a,(selecttablespace_name,sum(bytes) tot_usedfrom dba_segmentsgroup by tablespace_name ) bwhere 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)LOOPIf loop_emp.percentused > 70 thendbms_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 thendbms_output.put_line('Everything seems fine. Have Sufficient Space !!');elsedbms_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; /