OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P6: There is high demand for professionals in the information technology (IT) industry, and Oracle certifications are the hottest credential in the database world. You have made the right decision to pursue certification, because being Oracle Database 11g certified will give you a distinct advantage in this highly competitive market. | Utilizing Aggregate Functions 181 F single 47 M single 92 married 180 F married 63 M married 117 More DBA Queries In the Exploring DBA Queries Using Aggregate Functions sidebar you saw some queries written to find out the space allocated by tablespace the space allocated by schema and the space allocated by tablespace and schema. These were written using three different SQL statements. You can see the power of CUBE in the following SQL. The results from all the three SQL statements you tried before are in this summary report showing the different levels of aggregation. SELECT tablespace_name owner SUM bytes 1048576 size_mb FROM dba_segments GROUP BY CUBE tablespace_name owner TABLESPACE_NAME OWNER SIZE_MB - Grand Total HR - Subtotal HR schema IX - Subtotal IX schema OE FLOWS - Subtotal FLOWS schema USERS - Subtotal USERS tablespace USERS HR .1875 - HR schema in USERS tablespace USERS OE - OE schema in USERS tablespace USERS SH 2 USERS SCOTT .375 USERS BTHOMAS SYSAUX - Subtotal SYSAUX tablespace SYSAUX FLOWS SYSTEM - Subtotal SYSTEM tablespace SYSTEM SYS 182 Chapter 3 Using Group Functions SYSTEM OUTLN .5625 SYSTEM SYSTEM EXAMPLE EXAMPLE HR As you can see in the result the space used by each schema in each tablespace is shown as well as the total space used in each tablespace and the total space used by each schema. The total space used in the database including all tablespaces is also shown in the very first line. Three functions come in handy with the ROLLUP and CUBE modifiers of the GROUP BY clause GROUPING GROUP_ID and GROUPING_ID. In the examples you have seen using the ROLLUP and CUBE modifiers there was no way of telling which row is a subtotal and which row is a grand total. You can use the GROUPING function to overcome this problem. Review the following SQL example SELECT gender marital_status count num_rec GROUPING gender g_grp GROUPING .
