DBA_TABLESPACE_USAGE_METRICS and CDB_TABLES_ACES_USAGE_METRICS

DBA_TABLESPACE_USAGE_METRICS and CDB_TABLESPACE_USAGE_METRICS (starting 12c) provide us summary of space usage of each tablespace including TEMP and UNDO tablespaces. Only different between these 2 views is same as between all other CDB* and DBA* views whereby CDB* views contain information from all PDBs that is viewable from root container and have an extra column CON_ID to correspond each row to a particular PDB. Using this view makes it very easy to analyze space usage. In this article I would explain how to get information from these dictionary views to check the utilization of space within the tablespace(s). You may also want to read my articles about Tablespace space usage history and forecast for 12c and above, and for 11g and 10g.

Sizing information in this view is shown in the tablespace blocks. Therefore, first step is to check the block size of the tablespace so that space utilization can be correctly calculated. For example, if I want to check USERS tablespace utilization details, following will be the steps.

SQL> select block_size from dba_tablespaces where tablespace_name='USERS'; BLOCK_SIZE ----------       8192

Now we can query DBA_TABLESPACE_USAGE_METRICS by first converting values under TABLESPACE_SIZE and USED_SIZE into bytes (by multiplying with the block size) and then MBs or GBs whatever you want to see.

SQL> select (tablespace_size*8192)/1024/1024/1024 SIZE_GB,(used_space*8192)/1024/1024/1024 USED_GB,used_percent from dba_tablespace_usage_metrics where tablespace_name='USERS'; SIZE_GB     USED_GB       USED_PERCENT ---------- ---------- ------------ -------------------------- 63.9999695     47.2339325       73.8030547

In order to cross verify, you can query DBA_SEGMENTS to check the total segments size in this tablespace; the value would be same as shown in USED_GB column in above output.

SQL> select sum(bytes)/1024/1024/1024 from dba_segments where tablespace_name='USERS'; SUM(BYTES)/1024/1024/1024 -------------------------                47.2319336

There are a couple of things to remember: If datafiles are autoextensible, MAXSIZE is considered in calculating the value of TABLESPACE_SIZE column. For example, in my database USERS tablespace has two datafiles with current allocated size of 31.99 and 15.23 G respectively. Since both are autoextensible with MAXSIDE 31.99G, TABLESPACE_SIZE has a value of 63.99 G for this tablespace.

SQL>  select bytes/1024/1024/1024 SIZE_GB,maxbytes/1024/1024/1024 MAX_SIZE_GB,autoextensible from dba_data_files where tablespace_name='USERS';    SIZE_GB       MAX_SIZE_GB       AUT ---------- ----------- --- ------------------------- 31.9999847       31.9999847               YES 15.234375         31.9999847                YES SQL> select (tablespace_size*8192)/1024/1024/1024 SIZE_GB,(used_space*8192)/1024/1024/1024 USED_GB,used_percent from dba_tablespace_usage_metrics where tablespace_name='USERS'; SIZE_GB     USED_GB       USED_PERCENT ---------- ---------- ------------ -------------------------- 63.9999695     47.2339325       73.8030547

Another very important thing to note is that statistics in DBA_TABLESPACE_USAGE_METRIC are also influenced by underlying file system available space. Even if I have total tablespace 63.99 G in this case, if free space available on the file system is less than 63.99G, the value under TABLESPACE_SIZE will not be 63.99G, but some less value calculated after considering the available free space on the file system