Thursday, March 9, 2023

UNDO Tablespace disappear from dba_tablespace_usage_metrics


 I had a problem on 19c DBs where I cannot monitor the size of UNDO tablespace because it's now shown in dba_tablespace_usage_metrics view.

SQL> select * from dba_tablespace_usage_metrics;


It's Bug 28821847 - DBA_TABLESPACE_USAGE_METRICS Does Not Include Undo Tablespace Info ( Doc ID 28821847.8 ) impacting my current DB version 19.5 and it's fixed on 19.6 


I've modified my monitoring script to get the size of the UNDO tablespaces adding below green color script:

select tablespace_name,
       round((tablespace_size*8192)/(1024*1024)) Total_MB,
       round((used_space*8192)/(1024*1024)) Used_MB,
       round(((tablespace_size-used_space)*8192)/(1024*1024)) Free_MB,
       round(used_percent) "%Used"
from dba_tablespace_usage_metrics
 where tablespace_name not like '%UNDO%' union all SELECT a.tablespace_name,round(Total_MB) Total_MB,round(Used_MB) Used_MB,round ((Total_MB - Used_MB)) Free_MB,round ((Used_MB*100)/Total_MB,2) "%Used"
    ( SELECT SUM (maxbytes) / 1024 / 1024 Total_MB, b.tablespace_name
      FROM dba_data_files a, dba_tablespaces b
      WHERE a.tablespace_name = b.tablespace_name AND b.contents like 'UNDO'
      GROUP BY b.tablespace_name) a,
    ( SELECT c.tablespace_name, SUM (bytes) / 1024 / 1024 Used_MB
      WHERE status <> 'EXPIRED' GROUP BY c.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name

No comments:

Post a Comment