MattsBits
MattsBits

MattsBits

SQL Script To Display Tablespace Utilisation  

by Matt Hawkins, 26/08/2010
Categories : Oracle : APEX

This query will give you a basic report on the bytes used and bytes available in the Tablespaces within a particular schema. I have used this script to monitor tablespaces within Oracle APEX but it can be used elsewhere.

In this example the query looks at all Tablespaces that begin with "flow_".

If you wish to query all Tablespaces then remove the

AND lower(a.tablespace_name) LIKE 'flow_%'


from the WHERE clause.

The query is :

SELECT a.TABLESPACE_NAME,
       a.BYTES "bytes_used",
       b.BYTES "bytes_free",
       b.largest,
       round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) "percent_used" 
  FROM 
   (select TABLESPACE_NAME,sum(BYTES) BYTES 
      from dba_data_files 
     group by TABLESPACE_NAME) a,
   (select TABLESPACE_NAME,sum(BYTES) BYTES,max(BYTES) largest 
      from dba_free_space
     group by TABLESPACE_NAME) b 
 WHERE a.TABLESPACE_NAME=b.TABLESPACE_NAME AND 
       lower(a.tablespace_name) LIKE 'flow_%'

Author : Matt Hawkins  Last Edit By : Matt Hawkins
PHP Powered  MySQL Powered  Valid XHTML 1.0  Valid CSS  Firefox - Take Back The Web  EUKHost - Recommended Webhosting Solutions

MattHawkins CMS v3.0 - Copyright 2009-2017