 |
|
Computing Oracle percent (%) used for tablespaces
Oracle Tips by Burleson Consulting
|
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.
