Check for available free space

SELECT a.tablespace_name TSNAME, SUM(a.tots)/1048576 Tot_Size, 
 SUM(a.sumb)/1048576 Tot_Free, 
 SUM(a.sumb)*100/sum(a.tots) Pct_Free, 
 SUM(a.largest)/1048576 Large_Ext, SUM(a.chunks) Fragments 
FROM (SELECt tablespace_name, 0 tots, SUM(bytes) sumb, 
 MAX(bytes) largest, COUNT(*) chunks 
 FROM dba_free_space a 
 GROUP BY tablespace_name 
 SELECT tablespace_name, SUM(bytes) tots, 0, 0, 0 
 FROM dba_data_files 
 GROUP BY tablespace_name) a 
GROUP BY a.tablespace_name 
HAVING SUM(a.sumb)/1048576 < 10 
AND SUM(a.sumb)*100/sum(a.tots) < 30;

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: