Undo query using retention parameter

[hdbsystems]

Hello,

I think the query for the undo should take into account the retention parameter of the tablespace (GUARANTTE or NOGUARANTEE). My suggestion is the query below

What do you think?

best regards, sergio souza

col key format a60 col value format 999,999,999,999,999.99 set lines 150 set pages 150 select replace(a.key,'{#PDB}',d.name) key, a.value from v$database d, ( select 'undo_tbs[{#PDB},' || tablespace_name || ',' || case when k = 1 then 'filesize]' when k = 2 then 'maxsize]' when k = 3 then 'freebytes]' when k = 4 then 'usedbytes]' when k = 5 then 'pctfree]' when k = 6 then 'pctused]' when k = 7 then 'pctfreeMAX]' when k = 8 then 'pctusedMAX]' end key, case when k = 1 then file_size when k = 2 then file_max_size when k = 3 then file_size-file_used_space when k = 4 then file_used_space when k = 5 then round((file_size-file_used_space)/file_size100,2) when k = 6 then round((file_used_space)/file_size100,2) when k = 7 then round((file_size-file_used_space)/file_max_size100,2) when k = 8 then round((file_used_space+(file_max_size-file_size))/file_max_size100,2) end value from ( select t1.tablespace_name, t1.file_size, t1.file_max_size, case when t1.retention = 'GUARANTEE' then nvl(t2.active_file_used_space,0)+nvl(t2.unexpired_file_used_space,0) when t1.retention = 'NOGUARANTEE' then nvl(t2.active_file_used_space,0) end file_used_space
from ( select t.tablespace_name, t.retention, round(sum(f.bytes)) file_size, round(sum( case when f.autoextensible = 'NO' then f.bytes else greatest(f.bytes,f.maxbytes) end ) ) file_max_size from dba_data_files f, dba_tablespaces t where t.contents = 'UNDO' and t.tablespace_name = f.tablespace_name group by t.tablespace_name, t.retention ) t1, ( select * from ( select tablespace_name, status, bytes from dba_undo_extents ) pivot ( sum(bytes) as file_used_space for status in ('UNEXPIRED' as unexpired, 'EXPIRED' as expired, 'ACTIVE' as active) ) ) t2 where t1.tablespace_name = t2.tablespace_name(+) ) cross join ( select level k from dual connect by level <= 8 ) k ) a ;

[ikzelf]

Hi Sergio, this is for the primary.11.cfg .... can you make a pull request for that? I think it is a nice addition, that I did not test because of lack of v11 databases...

thanks, Ronald - who is happy more ppl are contributing

[ikzelf]

Hi Sergio, I added the query to primary.11.cfg it works in 12c but needs some modifications to get it working in multitennant mode.

[ikzelf]

BTW: added it in zbxdb, not zbxora