Oracle 在11g中具有内联视图错误的SQL，ORA-00979不是Group BY表达式，而是在12c中运行

嗨

在11.2.0.4.0上运行时，遵循SQL (正确地) 错误 “ORA-00979不是Group BY Expression”。
但是，当在12.1.0.2.0上运行时，SQL运行时没有错误，并返回total_tablespace_size的不正确信息-所有行返回0.5，而如果包含Group By，则获取带有0.5的几行和带有0.875的几行

select 
   s.tablespace_name
,  s.owner
,  s.segment_name
,  x.total_tablespace_size
,  SUM(s.bytes/1024/1024) "TOTAL_MB_USED" 
from 
   dba_segments S 
,  (select  SUM(s2.bytes/1024/1024) total_tablespace_size, s2.tablespace_name,s2.owner
   from dba_segments s2
   group by s2.tablespace_name,s2.owner
   ) x
where 
   s.owner = x.owner 
   and s.tablespace_name = x.tablespace_name
   and s.tablespace_name = 'USERS'
 group by
   s.tablespace_name
,  s.owner
,  s.segment_name
复制

这是12.1中的一个错误。固定在12.2

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

5 rows selected.

SQL> select
  2     s.tablespace_name
  3  ,  s.owner
  4  ,  s.segment_name
  5  ,  x.total_tablespace_size
  6  ,  SUM(s.bytes/1024/1024) "TOTAL_MB_USED"
  7  from
  8     dba_segments S
  9  ,  (select  SUM(s2.bytes/1024/1024) total_tablespace_size, s2.tablespace_name,s2.owner
 10     from dba_segments s2
 11     group by s2.tablespace_name,s2.owner
 12     ) x
 13  where
 14     s.owner = x.owner
 15     and s.tablespace_name = x.tablespace_name
 16     and s.tablespace_name = 'USERS'
 17   group by
 18     s.tablespace_name
 19  ,  s.owner
 20  ,  s.segment_name;
,  x.total_tablespace_size
   *
ERROR at line 5:
ORA-00979: not a GROUP BY expression
复制