Script for tablespace space monitoring for Windows Platform in Oracle

Script for tablespace space monitoring for Windows Platform in Oracle

Script as PL/SQL anonymous block:

set feedback off
set serveroutput on
v_flag VARCHAR2(1) := 0;
v_date date;
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"
(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,
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


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 !!');
dbms_output.put_line('Please contact DBA team !!');
end if;

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.