The DBA_TABLESPACE_USAGE_METRICS view is not really documented, but it's a handy
little view. Here's a quick description of it.
Let's do a query that shows which tablespaces are 80% filled up:
SQL> select * from dba_tablespace_usage_metrics
2 where used_percent >= 80 order by used_percent desc;
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
MYTS1 38140 38400 99.3229167
MYTS2 2248 2500 89.92
The most important columns are TABLESPACE_NAME and USED_PERCENT.
This easy
space usage query brings together PERMANENT, TEMPORARY, and UNDO tablespace
information into one simple view.
The columns of DBA_TABLESPACE_USAGE_METRICS are:
| 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 |
If the tablespace contains autoextensible data files, then TABLESPACE_SIZE will show:
- For Bigfile tablespaces: 4 GB number of blocks (4294967293)
- For ordinary tablespaces: Sum of the number of blocks in each data file, with each autoextensible data file contributing ~4 MB number of blocks (4194302)
You can join against DBA_TABLESPACES and do USED_SPACE * BLOCK_SIZE and
TABLESPACE_SIZE * BLOCK_SIZE to get the absolute space amounts in bytes.
You should have patch levels 10.1.0.5 or 10.2.0.2 installed in order to
avoid some bugs with DBA_TABLESPACE_USAGE_METRICS.
Keep in mind that this view is undocumented, and it could change without
notice in a future release.