5

What units are used in the usage metrics (USED_SPACE and TABLESPACE_SIZE columns) described in Oracle's DBA_TABLESPACE_USAGE_METRICS static data dictionary view?

The Oracle Database Reference only tells they are the "Total space consumed by the tablespace" and "Total size of the tablespace", without mentioning units. Are they in bytes? Megabytes? Blocks?

5

Blocks. Blocks are the units of the DBA_TABLESPACE_USAGE_METRICS.USED_SPACE and DBA_TABLESPACE_USAGE_METRICS.TABLESPACE_SIZE. The latter column accounts for possible AUTOEXTEND MAXSIZE.

EDIT: I'm not sure what is the meaning of USED_SPACE for undo tablespace though. For example:

SQL> SELECT tablespace_name, sum(blocks), status FROM dba_undo_extents GROUP BY tablespace_name, status ;

TABLESPACE_NAME                SUM(BLOCKS) STATUS
------------------------------ ----------- ---------
UNDOTBS1                               128 ACTIVE
UNDOTBS1                              5312 UNEXPIRED
UNDOTBS1                              8960 EXPIRED

SQL> select USED_SPACE from DBA_TABLESPACE_USAGE_METRICS WHERE TABLESPACE_NAME='UNDOTBS1';

USED_SPACE
----------
      2864

SQL> show parameter block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
  • 2
    indeed it is the block size (at least in version 11.2.0.2.0). it is more precise to lookup the tablespace blockssize at dba_tablespaces.block_size because a tablespace can have blocksize that differs from the default blocksize of the datababase – miracle173 Apr 6 '12 at 22:31
  • Indeed. Any thoughts regarding undo metrics? – kubanczyk Apr 10 '12 at 7:54
  • Undo blocks are always in the default block size for the database. Undo blocks aren't "before images" - they contain records telling Oracle how to undo changes to blocks. – Adam Musch Apr 10 '12 at 16:31
-1

I usually calculate so:

SELECT tbm.TABLESPACE_NAME,
   round(tbm.USED_SPACE * tb.BLOCK_SIZE /(1024*1024*1024),2) USED_SPACE_GB,
   round(tbm.TABLESPACE_SIZE * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_SIZE_GB,
   round((tbm.TABLESPACE_SIZE - tbm.USED_SPACE) * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_FREE_SIZE_GB,
   tbm.USED_PERCENT
FROM dba_tablespace_usage_metrics tbm
     join dba_tablespaces tb on tb.TABLESPACE_NAME = tbm.TABLESPACE_NAME
WHERE tbm.tablespace_name = 'tablespace_name'
  • This doesn't really answer the question; while your query makes an assumption as to the units involved, it doesn't provide any official evidence that your units are correct. – RDFozz Aug 18 '17 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.