Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

A question on ORA-00937: not a single-group group function error

martin75May 13 2008 — edited May 13 2008

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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 10 2008
Added on May 13 2008
1 comment
327 views