Check Tablespace Space Usage by Querying DBA_TABLESPACE_USAGE_METRICS

DBA need know a tablespace space usage.  There are a number of ways to do so by joining below different views:

DBA_TABLESPACE
DBA_DATA_FILES
DBA_FREE_SPACE
…
..
.

There is a quicker way to do so by querying DBA_TABLESPACE_USAGE_METRICS.

SQL> desc DBA_TABLESPACE_USAGE_METRICS
 Name               Null?   Type
 ----------------  -------  -----------
 TABLESPACE_NAME            VARCHAR2(30)
 USED_SPACE                 NUMBER
 TABLESPACE_SIZE            NUMBER
 USED_PERCENT               NUMBER

SQL> select * from DBA_TABLESPACE_USAGE_METRICS;

TABLESPACE_NAME  USED_SPACE   TABLESPACE_SIZE   USED_PERCENT
---------------- ------------ ----------------- ------------
PSAPP                6255360            7635328 81.9265394
PSAPPIDX             7025984           11028864 63.7054188
PSHUGE01            28452352           41633280 68.3404046
PSHUGE01IDX         33479424           46721792 71.6569775
PSHUGE02             4750856           11776000 40.3435462
PSHUGE02IDX          3838600            9728000 39.4592928
PSIBTRAN             1822832            2621440 69.5355225

Please note :

•  USED_SPACE and  TABLESPACE_SIZE are in blocks.
• TABLESPACE_SIZE  is the maximum possible size if AUTO extended on,  not the current size.  The same applies to USED_PERCENT.

Joining DBA_TABLESPACE_USAGE_METRICS with DBA_TABLESPACE,  we can get easily readable size in MB instead of default blocks.

SQL>SELECT a.tablespace_name,
  ROUND((a.used_space * b.block_size)/1024/1024, 2) AS "USED_SPACE(MB)",
  ROUND((a.tablespace_size * b.block_size)/1024/1024, 2) AS "TABLESPACE_SIZE(MB)",
  ROUND(a.used_percent, 2) AS "USED_PERCENT"
  FROM DBA_TABLESPACE_USAGE_METRICS a JOIN
  DBA_TABLESPACES b
  ON a.tablespace_name = b.tablespace_name;
 

TABLESPACE_NAME  USED_SPACE(MB) TABLESPACE_SIZE(MB) USED_PERCENT
---------------- -------------- ------------------- ------------
PSHUGE02IDX            29989.06            76000           39.46
PSHUGE02               37116.06            92000           40.34
PSAPPIDX               54890.5             86163           63.71
PSHUGE01              222284              325260           68.34
...
..
.

Sometimes we see “USED_PERCENT” is not refreshed immediately after datafiles are added or dropped. in this case, alter tablespace offline then online helps, if only you can do it.

TABLESPACE_NAME  USED_SPACE(MB) TABLESPACE_SIZE(MB) USED_PERCENT 
---------------- -------------- ------------------- ------------
TESTUSER_TBL       23.06          123                 18.75

SQL> alter tablespace TESTUSER_TBL offline;
Tablespace altered.

TABLESPACE_NAME  USED_SPACE(MB) TABLESPACE_SIZE(MB) USED_PERCENT 
---------------- -------------- ------------------- ------------
TESTUSER_TBL        22                  22            100