query to calculate total temp size in Oracle that works for all versions

Question

i am trying to frame a sql query which would on a database and return the total temp size( container temp size + pluggable temp size). The below query runs fine when the Oracle Database has a container and pluggable database i.e the latest versions.

select
      (select sum(bytes)/1024/1024/1024 from cdb_temp_files) + 
      (select sum(bytes)/1024/1024/1024 from dba_temp_files) as "Total Temp size(GB)" 
from dual;

The issue is that the query runs across several database versions -11g,12c,19c.So it fails in 11g saying 'cdb_temp_files' does not exist. is it possible to reframe the query such that it runs without any error on all versions of databases and returns the right data.

the query runs across several database versions -11g,12c,19c.It runs without any error on all versions of databases and returns the right data.

i have tried the below query,but it doesnt work as expected:

select
 (select case
         when exists (select 1 from cdb_temp_files)
         then sum(bytes)/1024/1024/1024
         else 0
        end
 from cdb_temp_files) as "Container_temp_size(GB)",
 (select case
         when exists (select 1 from dba_temp_files)
         then sum(bytes)/1024/1024/1024
         else 0
        end
 from dba_temp_files) as "Pluggable_temp_size(GB)",
(select case
         when exists (select 1 from cdb_temp_files)
         then sum(bytes)/1024/1024/1024
         else 0
        end
 from cdb_temp_files) +
 (select case
       when exists (select 1 from dba_temp_files)
   then sum(bytes)/1024/1024/1024
   else 0
   end
 from dba_temp_files) as "Total_temp_size(GB)" from dual;

Answer 1

There are several ways to generate dynamic results that depend on what objects are available. The most robust solutions require creating PL/SQL objects. But if you're just looking for one query that will quickly run on multiple databases, the DBMS_XMLGEN trick may work best for you.

Creating a query-within-a-query and then processing the XML output is difficult to code. But the upside is that you don't need to install any objects, and this query will quickly run on any database since version 9i.

--The total temp size from either DBA or DBA+CDB tables.
--
--#4: Get the size from the XMLType.
select temp_size_gb
from
(
    --#3: Convert the XML to an XMLType.
    select xmltype(xml_clob_results) xmltype_results
    from
    (
        --#2: Convert the SQL to XML.
        select dbms_xmlgen.getxml(v_sql) xml_clob_results
        from
        (
            --#1: Use data dictionary to create SQL statement based on which views exist.
            select
                'select ' || chr(10) || 
                listagg('    (select sum(bytes)/1024/1024/1024 from '||object_name||')', ' +' || chr(10)) within group (order by object_name) ||
                ' as temp_size_gb ' || chr(10) ||
                'from dual' v_sql
            from dba_objects
            where object_name in ('CDB_TEMP_FILES', 'DBA_TEMP_FILES')
                and object_type = 'VIEW'
        )
    )
    where xml_clob_results is not null
)
cross join xmltable
(
    '/ROWSET/ROW'
    passing xmltype_results
    columns
        TEMP_SIZE_GB number path 'TEMP_SIZE_GB'
);