适配全Oracle版本的临时表空间总容量查询SQL改写需求

需求：编写可在Oracle 11g、12c、19c多版本运行的SQL，查询临时表空间总容量（容器临时表空间容量+可插拔临时表空间容量）。原查询在高版本正常，但11g因cdb_temp_files表不存在报错，尝试的查询未达预期，需改写。

原查询代码 #

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;

尝试的查询代码 #

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;

改写后的跨版本SQL方案 #

核心思路是通过判断cdb_temp_files表是否存在，分分支执行查询，避免11g环境解析不存在的表导致报错：

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
WHERE EXISTS (SELECT 1 FROM all_tables WHERE table_name = 'CDB_TEMP_FILES')
UNION ALL
SELECT
    (SELECT SUM(bytes)/1024/1024/1024 FROM dba_temp_files) AS "Total Temp Size(GB)"
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM all_tables WHERE table_name = 'CDB_TEMP_FILES');

方案说明 #

• 在Oracle 12c及以上版本（支持CDB/PDB架构），all_tables中存在CDB_TEMP_FILES表，会执行第一个SELECT分支，将容器临时表空间与可插拔临时表空间容量相加。
• 在Oracle 11g版本，all_tables中无CDB_TEMP_FILES表，会执行第二个SELECT分支，仅查询dba_temp_files获取临时表空间总容量（11g无容器/可插拔区分）。
• 全程使用静态SQL，无需依赖PL/SQL，可直接在多版本环境执行且无报错。

内容的提问来源于stack exchange，提问作者Roshni Rabi