Avoid over-dependence on SQL*Plus with the Group By and Rollup SQL commands (8i+)

While the SQL*Plus language is an invaluable tool, it’s important not to get so dependent on it that you forget what to do if it isn’t available. For example, following is a nifty script that computes the amount of storage space (in bytes) taken up by your own database objects, such as tables or indexes. The script is composed of two parts. The first three lines are SQL*Plus commands. The next two lines make up the SQL query.

column sum(bytes) heading BYTES format 999,999
compute sum of bytes on report
break on report skip 1
select segment_name,bytes
from user_segments

If you run these lines in SQL*Plus or iSQL*Plus, the output appears like this:
SEGMENT_NAME       BYTES
---------------- --------
DEPT               65,536
EMP                65,536
                 --------
sum               131,072

But if you type this script in HTML DB, JDeveloper’s SQL Worksheet, Enterprise Manager’s SQL Scratchpad, or some non-Oracle application, you’ll probably get the error message ORA-00900: invalid SQL statement because of the three SQL*Plus commands at the top.

Fortunately, SQL is more powerful today than it was when SQL*Plus was originally designed. You can produce approximately the same report with straight SQL, as shown here:

select segment_name,
to_char(sum(bytes),'999G999') as BYTES
from user_segments
group by rollup (segment_name)

SEGMENT_NAME       BYTES
---------------- --------
DEPT               65,536
EMP                65,536
                  131,072