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-2022