Sunday, September 16, 2012

ORA-00979

This was tested on Oracle 11.1.0.6.0 on Windows XP. If you want to know how many tables, indexes and other object types belong to each user in the database, you might try something like this:

SQL> select owner, segment_type, count(*)
  2  from dba_segments
  3  group by owner;
select owner, segment_type, count(*)
              *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

SQL>

Oracle tries its best to point out the error but it does not always know what you were trying to do. In this case, line 1 is correct. If you want to know how many tables Fred owns, you clearly need to include: 
  • select owner (Fred etc.)
  • segment_type (table, index etc)
  • count(*) (how many of this owner / segment_type combination there are in the database).
Line 2 is also correct. The problem is in line 3 as it only tells Oracle to give a breakdown by owner. You can get the figures by owner and segment_type as follows:

SQL> l
  1  select owner, segment_type, count(*)
  2  from dba_segments
  3* group by owner, segment_type
SQL> /

OWNER                SEGMENT_TYPE           COUNT(*)
-------------------- -------------------- ----------
TSMSYS               TABLE                         1
SYS                  LOB PARTITION                 1
WMSYS                NESTED TABLE                  2
SYSMAN               NESTED TABLE                  2
FLOWS_FILES          LOBSEGMENT                    1
PM                   LOBINDEX                     17
WK_TEST              INDEX                        38
SCOTT                INDEX                         2
SYS                  INDEX                       868
OUTLN                INDEX                         4
OUTLN                LOBINDEX                      1
SYSTEM               TABLE                       108
SYSTEM               INDEX                       146
WMSYS                LOBSEGMENT                    8
XDB                  INDEX                        90
ORDSYS               LOBSEGMENT                    6
FLOWS_030000         LOBINDEX                     72
FLOWS_FILES          TABLE                         1
OE                   LOBSEGMENT                   15
OE                   INDEX                        31
PM                   INDEX                         3
IX                   LOBSEGMENT                    3
SH                   TABLE                        12
SH                   INDEX PARTITION             196
OUTLN                TABLE                         3
etc.

To make the figures easier to read, you would normally include an order by:

SQL> l

  1  select owner, segment_type, count(*)

  2  from dba_segments

  3  group by owner, segment_type
  4* order by owner, segment_type
SQL> /

OWNER                SEGMENT_TYPE           COUNT(*)
-------------------- -------------------- ----------
CTXSYS               INDEX                        52
CTXSYS               LOBINDEX                      2
CTXSYS               LOBSEGMENT                    2
CTXSYS               TABLE                        30
DBSNMP               INDEX                         8
DBSNMP               TABLE                        16
EXFSYS               INDEX                        38
EXFSYS               LOBINDEX                      2
EXFSYS               LOBSEGMENT                    2
EXFSYS               TABLE                        20
FLOWS_030000         INDEX                       504
FLOWS_030000         LOBINDEX                     72
FLOWS_030000         LOBSEGMENT                   72
FLOWS_030000         TABLE                       208
FLOWS_FILES          INDEX                         4
FLOWS_FILES          LOBINDEX                      1
FLOWS_FILES          LOBSEGMENT                    1
FLOWS_FILES          TABLE                         1
HR                   INDEX                        19
HR                   TABLE                         6
IX                   INDEX                        14
IX                   LOBINDEX                      3
IX                   LOBSEGMENT                    3
IX                   TABLE                         6
MDSYS                INDEX                        90
MDSYS                INDEX PARTITION               6
MDSYS                LOB PARTITION                 6
MDSYS                LOBINDEX                     75
MDSYS                LOBSEGMENT                   75
MDSYS                NESTED TABLE                  2
MDSYS                TABLE                        93
MDSYS                TABLE PARTITION               2
OE                   INDEX                        31
OE                   LOBINDEX                     15
OE                   LOBSEGMENT                   15
OE                   NESTED TABLE                  4
OE                   TABLE                        10
OLAPSYS              INDEX                       135
OLAPSYS              TABLE                       114
ORDSYS               INDEX                        78
ORDSYS               LOBINDEX                      6
ORDSYS               LOBSEGMENT                    6
ORDSYS               NESTED TABLE                  2
ORDSYS               TABLE                        50
OUTLN                INDEX                         4
OUTLN                LOBINDEX                      1
OUTLN                LOBSEGMENT                    1
OUTLN                TABLE                         3
etc.



There are a few examples to illustrate ORA-00979 in the book advertised above. One of them is shown below. As usual, click on the image to enlarge it and bring it into focus:


No comments: