beautifying output in sqlplus

Q:
Why SQLPLUS output is not coming properly?
How to get proper output in SQLPLUS?

A:

Here are some techniques to enhance the readability of sqlplus output.

Check this snap. The out put is really difficult to understand.


What can we do to make it readable?
First set the page size to a high values (it will be 6 by default). I used to set it to 100 so that i can see the headings after each 100 lines(including headings lines footers etc).
SQL>set pagesize 100
OR
SQL>set pages 100

If you don't want to see the heading at all. You can set
SQL>set pages 0
lets see the output after setting the page size.

Even now it is not much clear. Now lets set the line size also.I will set the line size to 150 because my screen can accommodate 150 letters in a single line
You can set it as
SQL>set linesize 150
OR
SQL>set lines 150



 Here we cant see much differences in the output apart from the line under FILE_NAME has became longer till the end of the screen. But still you have to do this because this will be the length of the line in your output after formatting the rest as well.

Here the output is coming like this because the column FILE_NAME is 513 bytes long.
Now we can format the column array size.
Now I am setting the column length of FILE_NAME to array of 30 characters. (you can do it depending up on the length of the names of the files in your output).
You can do it as
SQL> column file_name format a30
OR
SQL>col file_name for a30

Now things became under control :P

I am making the output a little more packed so that you don't have to scroll to see the entire outout. I am changing the lengths of the columns TABLESPACE_NAME & FILE_NAME



 OKEY?

Now we can go a little more additional. Here I have 11 rows selected, some of the tablespaces have more than one data file.  I want each tablespace names to be printed only once, it will be easy for me to identify the tablespaces. I will do like this.



Now I feel like calculating the sum of the sizes of each tablespaces here it self. You don't have to re write the query.!!!



 The out put will be a little confusing at the beginning but will be very useful for daily dba activities.
If you don't want to give your own label, you can simply type like
SQL>compute sum of max_gb on tablespace_name
here the label will be 'sum'

If you want the total sum, You can do like this.



 like sum, you can calculate average, max, min etc using this function.
for more information go to oracle documentation
(if you are getting page not found message, click at the address and press enter)

Hope it is helpful,

Thanks
alimajaf