Computing Oracle percent (%) used for tablespaces Oracle Database Tips by Donald Burleson

 

Question:  I hear that in Oracle10g it is no longer necessary to compute the % usage for tablespaces and datafiles.  How do you get the percent used in Oracle 10g?

 

Answer: Oracle provides numerous DBA views to show the percent used for Oracle objects:

 

• gv$filespace_usage RAC view

• v$filespace_usage view

• dba_tablespace_usage_metrics view

• dba_hist_tbspc_space_usage AWR table

 

Inside dba_tablespace_usage_metrics

 

The most useful for computing the percent used for tablespaces ids the dba_tablespace_usage_metrics view, which has pre-computed used_space and used_percent columns:

 

Column Name	Unit	Description
TABLESPACE_NAME	String	Name of the PERMANENT, TEMPORARY, or UNDO tablespace
USED_SPACE	Blocks	Used space, in blocks
TABLESPACE_SIZE	Blocks	Total data file space, in blocks
USED_PERCENT	Percentage	USED_SPACE / TABLESPACE_SIZE * 100

 

A query to display tablespaces with greater than 90 percent usage might look something like this:

select
   tablespace_name,
   used_percent
from
   dba_tablespace_usage_metrics
where
   used_percent > 90;

For computing space usage over time, you cannot beat the dba_hist_tbspc_space_usage table.  This AWR table allows you to compute a historical trend for tablespace growth, and use the data in a linear regression to predict when your tablespaces will extend. 

For more details on doing time-series analysis of space requirements, see my book "Oracle Tuning: The Definitive Reference".  In addition to complete explanations, it has dozens of working scripts in the instance code depot download.