Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

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
share|improve this answer
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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