Hi
Following SQL (correctly) errors with "ORA-00979 Not a Group BY Expression" when run on 11.2.0.4.0.
But when run on 12.1.0.2.0, SQL runs without error and returns incorrect information for total_tablespace_size - All rows return 0.5 whereas if include Group By, get several rows with 0.5 and several with 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
It's a bug in 12.1. Fixed in 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