Oracle DBMS - 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

Go back