I use the below query to find the space consumed by each Schema/user in an Oracle DB.
select owner, sum(bytes)/1024/1024 mb from dba_segments where owner in(select username from dba_users) GROUP BY owner order by mb desc;
and i get results like
OWNER MB
------------------------------ ----------
OLAPSYS 15.5625
EISPARI2006R10SP1 13.375
WMSYS 6.875
CTXSYS 4.625
EXFSYS 3.625
SCOTT 2.625
DBSNMP 1.5625
OUTLN .5
Now I want to include the total space consumed by all the schemas. How can i do include that? I would look something like the following(erroneous)
select owner, sum(bytes)/1024/1024 mb, SUM(sum(bytes)/1024/1024) TOTAL_SPACE_CONSUMED from dba_segments where owner in(select username from dba_users) GROUP BY owner order by mb desc;
ERROR at line 1:
ORA-00937: not a single-group group function