I’ve been using a query which utilizes both DBA_FREE_SPACE and DBA_DATA_FILES system views, but it often tends to be slow. Recently I’d come across another system view which is lightning fast – DBA_TABLESPACE_USAGE_METRICS.

It contains information about permanent, temporary, and undo tablespaces, and has only four columns:

• TABLESPACE_NAME
• USED_SPACE
• TABLESPACE_SIZE
• USED_PERCENT

There are three important things about those columns:

1. The numbers of USED_SPACE and TABLESPACE_SIZE are in blocks, not bytes, so we need to join the DBA_TABLESPACES system view to end up with numbers in bytes.
2. The TABLESPACE_SIZE column shows the total possible size, not the current size.
3. USED_PERCENT shows usage of total possible size of a tablespace.

Here is the useful query that I now use (notice the multiplication by BLOCK_SIZE):

SELECT
  a.tablespace_name,
  ROUND((a.used_space * b.block_size) / 1048576, 2) AS "Used space (MB)",
  ROUND((a.tablespace_size * b.block_size) / 1048576, 2) AS "Tablespace size (MB)",
  ROUND(a.used_percent, 2) AS "Used %"
FROM dba_tablespace_usage_metrics a
  JOIN dba_tablespaces b ON a.tablespace_name = b.tablespace_name
;

Output:

TABLESPACE_NAME   Used space (MB) Tablespace size (MB)     Used %
----------------- --------------- -------------------- ----------
EXAMPLE           70,94           3639,52              1,95
SYSAUX            1179,63         4826,52              24,44
SYSTEM            843,31          4408,52              19,13
TEMP              0               3768,52              0
UNDOTBS1          1,69            3727,52              0,05
USERS             429,13          4010,02              10,7

Now, about the points 2. and 3. – if I create two new tablespace, one with AUTOEXTEND ON, and the other without, that’s what the query will produce:

CREATE TABLESPACE TEST_ON DATAFILE 
  '/home/oracle/app/oracle/oradata/test_on.dbf' SIZE 10M AUTOEXTEND ON;
CREATE TABLESPACE TEST_OFF DATAFILE
  '/home/oracle/app/oracle/oradata/test_off.dbf' SIZE 10M AUTOEXTEND OFF;

TABLESPACE_NAME   Used space (MB) Tablespace size (MB)     Used %
----------------- --------------- -------------------- ----------
EXAMPLE           70,94           3639,52              1,95
TEST_ON           1               3547,45              0,03
TEST_OFF          1               10                   10
SYSAUX            1179,63         4826,52              24,44
SYSTEM            843,31          4408,52              19,13
TEMP              0               3768,52              0
UNDOTBS1          1,69            3727,52              0,05
USERS             429,13          4010,02              10,7

As you may see, in case of the TEST_ON tablespace, we see the total possible size as the "Tablespace size (MB)", which is 3547,45, and the "Used %" shows 0,03. The TEST_OFF tablespace’s maximum size is only 10 MB (AUTOEXTEND OFF), so the "Used %" is 10%.

I hope you’ll find the query useful. Here is the link do Oracle’s documentation about the view:

Oracle Documentation – DBA_TABLESPACE_USAGE_METRICS